Rebuilding indexes on geodatabase system tables using a Python script

This topic applies to ArcGIS for Desktop Standard and ArcGIS for Desktop Advanced only.

Database indexes are used to quickly identify rows that meet a query's predicate filter. Most of the geodatabase system tables have indexes, but the tables that tend to have the greatest amount of change in a versioned enterprise geodatabase and, therefore, require the indexes to be rebuilt most often are the states, state_lineages, and mv_tables_modified system tables. In a heavily edited versioned geodatabase, you might update indexes on these tables nightly.

As the geodatabase administrator, you can create a stand-alone Python script that calls the Rebuild Indexes tool and schedule it to run to rebuild the indexes on these tables in geodatabases in IBM DB2, Microsoft SQL Server, Oracle, or PostgreSQL.

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.

Steps:
  1. Copy one of the following scripts to a computer where Python and one of the following ArcGIS clients are installed:
    • ArcGIS for Desktop (Standardor Advanced)
    • ArcGIS Engine with the Geodatabase Update extension
    • ArcGIS Runtime
    • ArcGIS for Server (Standard or Advanced)

    Alter the scripts with information specific to your site.

    This script uses an existing database connection file on the local machine to connect to the database and run the script:

    # Name: RSysIdx.py
# Description: Rebuilds indexes on the states, state_lineages, 
# and mv_tables_modified tables in an enterprise geodatabase 
# using an existing .sde file.

# Author: Esri

# Import system modules
import arcpy, os

# set workspace
workspace = arcpy.GetParameterAsText(0)

# set the workspace environment
arcpy.env.workspace = workspace

# Execute rebuild indexes
# Note: To use the "SYSTEM" option the workspace user must be an administrator.
arcpy.RebuildIndexes_management(workspace, "SYSTEM", "", "ALL")
print 'Rebuild Complete'

    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.

# Author: Esri

# Import system modules
import sys
import arcpy
import os

# Provide connection information
server = servername
service = "5151 | sde:oracle:sid"
account_authentication = OPERATING_SYSTEM_AUTH | DATABASE_AUTH
#Leave username and password blank if using OPERATING_SYSTEM_AUTH
username = gdb_admin_user_name
password = gdb_admin_password
version = sde.DEFAULT


# 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
saveVersionInfo = "SAVE_VERSION" #DO_NOT_SAVE_VERSION

print "Creating ArcSDE Connection File..."
# Create ArcSDE Connection File
# Usage: out_folder_path, out_name, server, service, database, account_authentication, username, password, save_username_password
arcpy.CreateArcSDEConnectionFile_management(temp, "connection.sde", server, service, account_authentication, username, password, saveUserInfo, version, saveVersionInfo)

# 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.

# Author: Esri

# Import system modules
import sys
import arcpy
import os

# Provide connection information
server = servername
service = "5151 | sde:sqlserver:sqlserver_instance"
database = database_name
account_authentication = OPERATING_SYSTEM_AUTH | DATABASE_AUTH
#Leave username and password blank if using OPERATING_SYSTEM_AUTH
username = gdb_admin_user_name
password = gdb_admin_password
version = sde.DEFAULT


# 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
saveVersionInfo = "SAVE_VERSION" #DO_NOT_SAVE_VERSION

print "Creating ArcSDE Connection File..."
# Create ArcSDE Connection File
# Usage: out_folder_path, out_name, server, service, database, account_authentication, username, password, save_username_password
arcpy.CreateArcSDEConnectionFile_management(temp, "connection.sde", server, service, database, account_authentication, username, password, saveUserInfo, version, saveVersionInfo)

# 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.

# Author: Esri

# Import system modules
import sys
import arcpy
import os

# Provide connection information
server = servername
service = "5151 | sde:postgresql:servername"
database = database_name
account_authentication = DATABASE_AUTH
username = gdb_admin_user_name
password = gdb_admin_password
version = sde.DEFAULT


# 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
saveVersionInfo = "SAVE_VERSION" #DO_NOT_SAVE_VERSION

print "Creating ArcSDE Connection File..."
# Create ArcSDE Connection File
# Usage: out_folder_path, out_name, server, service, database, account_authentication, username, password, save_username_password
arcpy.CreateArcSDEConnectionFile_management(temp, "connection.sde", server, service, database, account_authentication, username, password, saveUserInfo, version, saveVersionInfo)

# 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.

# Author: Esri

# Import system modules
import sys
import arcpy
import os

# Provide connection information
server = servername
service = "5151 | sde:db2"
database = db_alias
account_authentication = OPERATING_SYSTEM_AUTH | DATABASE_AUTH
#Leave username and password blank if using OPERATING_SYSTEM_AUTH
username = gdb_admin_user_name
password = gdb_admin_password
version = sde.DEFAULT


# 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
saveVersionInfo = "SAVE_VERSION" #DO_NOT_SAVE_VERSION

print "Creating ArcSDE Connection File..."
# Create ArcSDE Connection File
# Usage: out_folder_path, out_name, server, service, database, account_authentication, username, password, save_username_password
arcpy.CreateArcSDEConnectionFile_management(temp, "connection.sde", server, service, database, account_authentication, username, password, saveUserInfo, version, saveVersionInfo)

# Rebuild indexes on system tables
arcpy.RebuildIndexes_management(Connection_File_Name, "SYSTEM", "", "ALL")
print 'Rebuild Complete'

    In this example, the sde user connects to an Informix database:

    # Name: RSysIdxIDS.py
# Description: Rebuilds indexes on the states, state_lineages,
# and mv_tables_modified tables in an enterprise geodatabase
# in Informix IDS.

# Author: Esri

# Import system modules
import sys
import arcpy
import os

# Provide connection information
server = servername
service = "5151 | sde:informix"
database = odbc_dsn
account_authentication = OPERATING_SYSTEM_AUTH | DATABASE_AUTH
#Leave username and password blank if using OPERATING_SYSTEM_AUTH
username = gdb_admin_user_name
password = gdb_admin_password
version = sde.DEFAULT


# 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
saveVersionInfo = "SAVE_VERSION" #DO_NOT_SAVE_VERSION

print "Creating ArcSDE Connection File..."
# Create ArcSDE Connection File
# Usage: out_folder_path, out_name, server, service, database, account_authentication, username, password, save_username_password
arcpy.CreateArcSDEConnectionFile_management(temp, "connection.sde", server, service, database, account_authentication, username, password, saveUserInfo, version, saveVersionInfo)

# Rebuild indexes on system tables
arcpy.RebuildIndexes_management(Connection_File_Name, "SYSTEM", "", "ALL")
print 'Rebuild Complete'
  2. After you alter the script to contain your connection information, schedule the script to run at a specific time each night.
    • On Windows, 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.
    • On Linux, create a cron text file that contains information on the day and time you want the script to run, then 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 on using cron.
Copyright © 1995-2012 Esri. All rights reserved.
10/24/2012