使用 Python 脚本重新构建地理数据库系统表的索引

本主题仅适用于 ArcGIS for Desktop Standard 和 ArcGIS for Desktop Advanced。

数据库索引用于快速确定符合查询谓词过滤器的行。大多数地理数据库系统表都具有索引，但在版本化企业级地理数据库中，states、state_lineages 和 mv_tables_modified 系统表很容易会有大量更改，因此最经常需要重新构建索引。在编辑量大的版本化地理数据库中，可以在夜间更新这些表的索引。

作为地理数据库管理员，您可以创建调用重建索引工具的独立 Python 脚本，并安排其运行以对 IBM DB2、Microsoft SQL Server、Oracle 或 PostgreSQL 中的地理数据库的这些表重新构建索引。

要运行该脚本，您必须能够以地理数据库管理员身份连接到地理数据库。您可以创建连接文件 (.sde) 并从脚本指向该文件，或者直接在脚本中键入连接信息。下一步，使用“Windows 计划任务”或 Linux cron 后台程序安排该脚本运行。

步骤:
  1. 将以下脚本之一复制到已安装 Python 和下列 ArcGIS 客户端之一的计算机：
    • ArcGIS for Desktop（标准版或高级版）
    • 具有 Geodatabase Update 扩展模块的 ArcGIS Engine
    • ArcGIS Runtime
    • ArcGIS for Server（标准版或高级版）

    使用特定于您的站点的信息修改脚本。

    以下脚本使用本地计算机上现有的数据库连接文件连接到数据库并运行脚本：

    # 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)
default_gdb = "C:\\Documents and Settings\<user>\Application Data\ESRI\ArcCatalog\sp_data.sde"
# 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'

    以下示例脚本包含连接到 Oracle 数据库以更新 states、state_lineages 和 mv_tables_modified 系统表索引所需的信息：

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

    以下示例脚本包含使用操作系统验证的 dbo 用户连接到 SQL Server 并更新 sde_states、sde_state_lineages 和 sde_mv_tables_modified 系统表索引所需的信息：

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

    在以下示例中，sde 用户连接到 PostgreSQL 数据库：

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

    在以下示例中，sde 用户连接到 DB2 数据库：

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

    在以下示例中，sde 用户连接到 Informix 数据库：

    # 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. 修改脚本以包含连接信息后，可安排脚本在每晚特定的时间运行。
    • 在 Windows 中，从控制面板打开“计划任务”并使用向导添加计划任务。当系统询问要运行哪个程序时，浏览至您的 Python 脚本。
    • 在 Linux 中，创建一个 cron 文本文件，其中包含希望脚本运行的日期和时间信息，然后使用 crontab 程序将该文件加载到 cron 中。

      例如，以下信息将 Python 脚本（名为 rsysidxdb2.py）设置为在每个星期三晚上 10:00 运行：

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

      有关 cron 的使用信息，请参阅随 Linux 安装提供的 Linux 手册页。
Copyright © 1995-2013 Esri. All rights reserved.
9/15/2013