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 mv_tables_modified system tables. As the geodatabase administrator, you can rebuild the indexes on these tables in geodatabases in IBM Db2, Microsoft SQL Server, Oracle, or PostgreSQL using the Rebuild Indexes geoprocessing tool.
In a heavily edited geodatabase that use traditional versioning, you might update indexes on the states, state_lineages, and mv_tables_modified tables nightly. To do that, create a stand-alone Python script that calls the Rebuild Indexes tool and schedule it to run using Windows Scheduled Tasks or a cron job.
Use the Rebuild Indexes tool
To rebuild indexes on the states, state_lineages, and mv_tables_modified geodatabase system tables using the Rebuild Indexes tool, do the following:
- Start ArcGIS Pro and connect to the geodatabase as the geodatabase administrator.
- Open the Rebuild Indexes geoprocessing tool.
This tool can be found in the Geodatabase Administration toolset of the Data Management toolbox.
- Use the connection you created in step 1 as the Input Database Connection.
- Check the Include System Tables check box.
- Uncheck Rebuild Delta Tables Only.
- 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 point to that from the script or type the connection information directly in the script. Next, schedule the script to run using Windows Scheduled Tasks or the Linux cron daemon.
- Copy one of the following scripts to a computer where Python and one of the following ArcGIS products are installed:
- ArcGIS Pro (Desktop Standard or Desktop Advanced)
- ArcGIS Desktop (Desktop Standard or Desktop Advanced)
- ArcGIS Engine with the Geodatabase Update extension
- ArcGIS Server
Alter the scripts with information specific to your site.
This sample script contains the information necessary to connect to an Oracle database to update the indexes on the states, state_lineages, and mv_tables_modified system tables:
# Name: RSysIdxOracle.py # Description: Rebuilds indexes on the states, state_lineages, # and mv_tables_modified tables in an enterprise geodatabase # in Oracle. # Import system modules import sys import arcpy import os # Provide connection information platform = ORACLE instance = "myserver/orcl" account_authentication = OPERATING_SYSTEM_AUTH | DATABASE_AUTH #Leave username and password blank if using OPERATING_SYSTEM_AUTH username = gdb_admin 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")
This sample script contains information to use an operating system authenticated dbo user to connect to SQL Server and update indexes on the sde_states, sde_state_lineages, and sde_mv_tables_modified system tables:
# Name: RSysIdxSqlServer.py # Description: Rebuilds indexes on the sde_states, sde_state_lineages, # and sde_mv_tables_modified tables in an enterprise geodatabase # in SQL Server. # Import system modules import sys import arcpy import os # Provide connection information platform = SQL_SERVER instance = sqlserver_instance_name account_authentication = OPERATING_SYSTEM_AUTH database = database_name # 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) print ("Creating Database Connection File...") # Create Database Connection File # Usage: out_folder_path, out_name, database_platform, instance, account_authentication, database arcpy.CreateDatabaseConnectionFile_management(temp, "connection.sde", platform, instance, account_authentication, database) # Rebuild indexes on system tables arcpy.RebuildIndexes_management(Connection_File_Name, "SYSTEM", "", "ALL") print ("Rebuild Complete")
In this example, the sde user connects to a PostgreSQL database:
# Name: RSysIdxpg.py # Description: Rebuilds indexes on the sde_states, sde_state_lineages, # and sde_mv_tables_modified tables in an enterprise geodatabase # in PostgreSQL. # Import system modules import sys import arcpy import os # Provide connection information platform = POSTGRESQL instance = pg_cluster account_authentication = DATABASE_AUTH username = gdb_admin password = gdb_admin_password database = database_name # 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, database arcpy.CreateDatabaseConnectionFile_management(temp, "connection.sde", platform, instance, account_authentication, username, password, saveUserInfo, database) # Rebuild indexes on system tables arcpy.RebuildIndexes_management(Connection_File_Name, "SYSTEM", "", "ALL") print ("Rebuild Complete")
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")
- After you alter the script to contain your connection information, schedule the script to run at a specific time each night. Open Scheduled Tasks from the Control Panel and use the wizard to add a scheduled task. When asked what program to run, browse to your Python script.