The database management system optimizer uses database statistics to choose the optimal execution plan for queries. To maintain query performance, you should update statistics after the content of a table changes significantly; for example, after you load a large amount of records to it or perform a lot of edits on the table.
Statistics can be updated on the following types of tables and indexes:
- The table you selected to analyze and its associated indexes
- The delta tables of versioned tables and feature classes and the indexes on the delta tables
- The historical archive tables of datasets that are enabled for archiving, and the archive tables' indexes
- Geodatabase system tables
- If analyzing a feature class that uses sdebinary geometry storage, statistics are also updated on the feature table and spatial index table and these tables' indexes
There are several methods in ArcGIS you can use to update statistics. These are described in the following sections:
Use the Analyze command
You can use the Analyze command in ArcMap to update statistics on specific datasets that you select directly in the Catalog tree. You must be the dataset owner to update statistics.
- Start ArcMap or ArcCatalog.
-
Under Database Connections in the Catalog tree, connect to the database or enterprise geodatabase that contains the dataset for which you want to update statistics.
Be sure to connect as the owner of the dataset.
- Right-click the dataset in the Catalog tree.
This can be a feature dataset, feature class, table, raster catalog, raster dataset, or mosaic dataset.
- Point to Manage and click Analyze.
Statistics are updated for tables and indexes on this dataset as described at the beginning of this topic.
Use the Analyze Datasets tool
You can use the Analyze Datasets geoprocessing tool (located in the Geodatabase Administration toolset of the Data Management toolbox) to update statistics. Use this tool instead of the Analyze command in ArcMap if you want to update statistics on several datasets at once, if you want to choose the specific tables that are analyzed (for example, if you only want to update statistics on the delta tables), or if you want to update statistics on the geodatabase system tables.
You must run the tool as the owner of the tables you want to analyze. In the case of geodatabase system tables, you must run the tool as the geodatabase administrator. You should update statistics on the geodatabase system tables after many new tables or feature classes have been added to the geodatabase, a large number of versioned edits have been performed, or the geodatabase has been compressed.
- Start an ArcGIS for Desktop client and connect to the geodatabase that contains the tables for which you want to update statistics.
Connect as the owner of the tables.
- Open the Analyze Datasets geoprocessing tool.
You can type the tool name into the Search field in an ArcGIS for Desktop client to find the tool and open it, or browse to the tool in the Geodatabase Administration toolset of the Data Management toolbox.
- Use the database connection file you created in step 1 as the input workspace.
- Check which tables you want analyzed:
- If you want statistics updated for the selected base tables, leave Analyze Base Tables for Selected Dataset(s) checked.
- Check Analyze Delta Tables for Selected Dataset(s) to update statistics on the adds and deletes tables of versioned datasets.
- Check Analyze Archive Tables for Selected Dataset(s) to update statistics on the historical archive tables of datasets that are enabled for archiving.
- Check Include System Tables to update statistics on geodatabase system tables.
- Click OK to run the tool.
Statistics are updated for the tables you chose and their indexes.
Run a Python script
You can call the AnalyzeDatasets_management function in a Python script to update statistics on base tables, delta tables, archive tables, and/or geodatabase system tables. Just as with the Analyze Datasets tool, you must run the AnalyzeDatasets_management as the owner of the tables you are analyzing.
When you run the following Python script, specify a database connection file (.sde file) when you run it. The script updates statistics for all tables owned by the user specified in the connection.
For example, to run this script on Linux using the database connection file entgdb.sde, located in /usr/connections, type the following:
./ AnalyzeDatasets.py /usr/connections/entgdb.sde
To run this script to update statistics on system tables, specify a connection file that connects as the geodatabase administrator.
# Name: AnalyzeDatasets.py
# Description: analyzes all datasets in an enterprise geodatabase
# for a given user.
# Import system modules
import arcpy, os
# set workspace
# the user in this workspace must be the owner of the data to analyze.
workspace = arcpy.GetParameterAsText(0)
# set the workspace environment
arcpy.env.workspace = workspace
# NOTE: Analyze Datasets can accept a Python list of datasets.
# Get a list of all the datasets the user has access to.
# First, get all the stand alone tables, feature classes and rasters.
dataList = arcpy.ListTables() + arcpy.ListFeatureClasses() + arcpy.ListRasters()
# Next, for feature datasets get all of the datasets and featureclasses
# from the list and add them to the master list.
for dataset in arcpy.ListDatasets("", "Feature"):
arcpy.env.workspace = os.path.join(workspace,dataset)
dataList += arcpy.ListFeatureClasses() + arcpy.ListDatasets()
# reset the workspace
arcpy.env.workspace = workspace
# Get the user name for the workspace
userName = arcpy.Describe(workspace).connectionProperties.user.lower()
# remove any datasets that are not owned by the connected user.
userDataList = [ds for ds in dataList if ds.lower().find(".%s." % userName) > -1]
# Execute analyze datasets
# Note: to use the "SYSTEM" option the workspace user must be an administrator.
arcpy.AnalyzeDatasets_management(workspace, "NO_SYSTEM", userDataList, "ANALYZE_BASE","ANALYZE_DELTA","ANALYZE_ARCHIVE")
print "Analyze Complete"