Rebuild system table indexes

Available with Standard or Advanced license.

The database uses indexes to quickly identify rows when a client queries the database. Most of the geodatabase system tables have indexes, but the tables that tend to have the greatest amount of change in an enterprise geodatabase that uses traditional versioning and, therefore, require the indexes to be rebuilt most often are the STATES, STATE_LINEAGES, and MVTABLES_MODIFIED system tables. As the geodatabase administrator, you can rebuild the indexes on these tables in enterprise geodatabases using the Rebuild Indexes geoprocessing tool.

In a heavily edited geodatabase that uses traditional versioning, you might update indexes on the STATES, STATE_LINEAGES, and MVTABLES_MODIFIED tables nightly. To do that, create a stand-alone Python script that calls the Rebuild Indexes tool, and schedule it to run using Microsoft Windows Scheduled Tasks or a cron job.

Use the Rebuild Indexes tool

To rebuild indexes on the STATES, STATE_LINEAGES, and MVTABLES_MODIFIED geodatabase system tables using the Rebuild Indexes tool, complete the following steps:

1. Start ArcGIS Pro, and connect to the geodatabase as the geodatabase administrator.
2. Open the Rebuild Indexes geoprocessing tool.

   This tool is in the Geodatabase Administration toolset of the Data Management toolbox.

3. Use the connection you created in step 1 as the Input Database Connection parameter value.
4. Check the Include System Tables parameter.
5. Uncheck the Rebuild Delta Tables Only parameter.
6. Click Run.

Schedule a Python script

To run the script, you must be able to connect to the geodatabase as the geodatabase administrator. You can either create a connection file (.sde) and specify it in the script or type the connection information directly in the script. Next, schedule the script to run using Microsoft Windows Scheduled Tasks or the Linux cron daemon.

1. Copy one of the following scripts to a computer where Python and one of the following ArcGIS products are installed:
   • ArcGIS Pro (Standard or Advanced)
   • ArcGIS Server

   Alter the scripts with information specific to your site.

   In this example, the sde user connects to a Db2 database:

   # Name: RSysIdxDb2.py
   # Description: Rebuilds indexes on the states, state_lineages,
   # and mv_tables_modified tables in an enterprise geodatabase
   # in DB2.
   
   # Import system modules
   import sys
   import arcpy
   import os
   
   # Provide connection information
   platform = DB2
   instance = db2gdb
   account_authentication = OPERATING_SYSTEM_AUTH | DATABASE_AUTH
   #Leave username and password blank if using OPERATING_SYSTEM_AUTH
   username = sde
   password = gdb_admin_password
   
   
   # Set local variables
   if os.name.lower() == "nt":
      slashsyntax = "\\"
      if os.environ.get("TEMP") == None:
         temp = "c:\\temp"
      else:
         temp = os.environ.get("TEMP")
   else:
      slashsyntax = "/"
      if os.environ.get("TMP") == None:
         temp = "/usr/tmp"
      else:
         temp = os.environ.get("TMP")
   
   Connection_File_Name = temp + slashsyntax + "connection.sde"
   
   # Check for the .sde file and delete it if present
   if os.path.exists(Connection_File_Name):
      os.remove(Connection_File_Name)
   
   #Variable defined within the script; other variable options commented out at the end of the line
   saveUserInfo = "SAVE_USERNAME" #DO_NOT_SAVE_USERNAME
   
   print ("Creating Database Connection File...")
   # Create Database Connection File
   # Usage: out_folder_path, out_name, database_platform, instance, account_authentication, username, password, save_user_pass
   arcpy.CreateDatabaseConnectionFile_management(temp, "connection.sde", platform, instance, account_authentication, username, password, saveUserInfo)
   
   # Rebuild indexes on system tables
   arcpy.RebuildIndexes_management(Connection_File_Name, "SYSTEM", "", "ALL")
   print ("Rebuild Complete")

2. After you alter the script to contain the connection information, schedule the script to run at a specific time each night by doing one of the following:
   • On Windows, open Scheduled Tasks from the Control Panel, and use the wizard to add a scheduled task. When prompted for the program to run, browse to the Python script you created.
   • For an ArcGIS Server installation on Linux, create a cron text file that contains information for the day and time you want the script to run, and load the file into cron using the crontab program.

     For example, the following information sets the Python script (named rsysidxdb2.py) to run every Wednesday at 10:00 p.m.:

     0 22 * * 3 /usr/bin/rsysidxdb2.py

     See the Linux man pages provided with your Linux installation for information about using cron.