在 Standard 或 Advanced 许可等级下可用。
当客户端查询数据库时,数据库将通过索引快速识别行。大多数地理数据库系统表都具有索引,但在使用传统版本化的企业级地理数据库中,states、state_lineages 和 mv_tables_modified 系统表很容易会有大量更改,因此最经常需要重新构建索引。 作为数据库管理员,您可以使用重建索引地理处理工具对 IBM Db2、Microsoft SQL Server、Oracle 或 PostgreSQL 中的地理数据库的这些表重新构建索引。
在编辑量大且使用传统版本化的地理数据库中,可以在夜间更新 states、state_lineages 和 mv_tables_modified 表的索引。要执行此操作,可以创建独立 Python 脚本,此脚本可调用重建索引工具,并使用 Windows 计划任务或 cron 作业安排其运行。
使用重建索引工具
要使用重建索引工具重新构建 states、state_lineages 和 mv_tables_modified 地理数据库系统表的索引,请执行以下操作:
- 启动 ArcGIS Pro 并以地理数据库管理员身份连接到地理数据库。
- 打开重建索引地理处理工具。
该工具位于“数据管理”工具箱的“地理数据库管理”工具集中。
- 使用步骤 1 中创建的连接作为输入数据库连接。
- 选中包括系统表复选框。
- 取消选中仅重建增量表。
- 单击运行。
计划 Python 脚本
要运行该脚本,您必须能够以地理数据库管理员身份连接到地理数据库。您可以创建连接文件 (.sde) 并从脚本指向该文件,或者直接在脚本中键入连接信息。下一步,使用“Windows 计划任务”或 Linux cron 后台程序安排该脚本运行。
- 将以下脚本之一复制到已安装 Python 和下列 ArcGIS 产品之一的计算机:
- ArcGIS Pro(Desktop Standard 或 Desktop Advanced)
- ArcGIS Desktop(Desktop Standard 或 Desktop Advanced)
- ArcGIS Engine 和 Geodatabase Update 扩展模块
- ArcGIS Server
使用特定于您的站点的信息修改脚本。
以下示例脚本包含连接到 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. # 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")
以下示例脚本包含使用操作系统验证的 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. # 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")
在以下示例中,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. # 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")
在以下示例中,sde 用户连接到 Db2 数据库:
# 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")
- 修改脚本以包含连接信息后,可安排脚本在每晚特定的时间运行。从控制面板打开“计划任务”并使用向导添加计划任务。当系统询问要运行哪个程序时,浏览至您的 Python 脚本。