How to rebuild spatial indexes in batches for the element classes of the ArcSDE Database

We have encountered many performance problems. We generally recommend that you reread the spatial index. If you have dozens or even hundreds of spatial indexes in a database, what should you do? ArcGIS10.1 version RebuildIndexes: (this function is available only for ArcGIS10.1) this function is mainly used to edit a wide range of data for users,

We have encountered many performance problems. We generally recommend that you reread the spatial index. If you have dozens or even hundreds of spatial indexes in a database, what should you do? ArcGIS10.1 version RebuildIndexes: (this function is available only for ArcGIS10.1) this function is mainly used to edit a wide range of data for users,

We have encountered many performance problems. We generally recommend that you reread the spatial index. If you have dozens or even hundreds of spatial indexes in a database, what should you do?

ArcGIS10.1

RebuildIndexes: (this function is available only for ArcGIS10.1)
This function is mainly used to edit a large range of data for users. After a large amount of data is loaded or deleted based on the original data, operations are performed to improve data performance. In fact, it is a bit old-fashioned here. Our original method is to re-index a layer by layer, but this function canBatch re-IndexingAnd supports attribute indexes as well as spatial indexes. It is convenient to re-index system tables and version incremental tables.

Versions earlier than ArcGIS10

However, for ArcGIS10 and earlier versions, there is no way to rebuild indexes in batches. We can only find another way.

1: Method of Using sde command line

We can use the sde command, load_only_io and normal_io to switch to recreate the spatial index. For details, see help.

Switch between load only and normal I/O modes.To modify a feature class's input/output mode, use the load_only_io and normal_io operations.You must be the owner of the feature class to change it from normal I/O to load-only I/O mode.It is recommended that you do not place a versioned feature class that uses binary storage in load only I/O mode because, when you switch back to normal I/O mode, the spatial index will be calculated on a versioned representation of the features. This representation may not match what is stored in the nonversioned f table to which the index gets applied. If this is the case, an error is returned.When the sdelayer command is used to create a layer (i.e., if the register or add operations are used), the resultant feature class is automatically in normal I/O mode. The load-only I/O mode is provided to make bulk data loading processes more efficient. Use load-only mode when performing large inserts to avoid the continuous update of the feature class's indexes.For feature classes that use a spatial grid index (SDEBINARY, SDELOB, WKB_GEOMETRY, or feature classes in DB2), if the grid fields are updated while the feature class is in load-only I/O mode, the spatial index is rebuilt with the new grid sizes when you reset the feature class to normal I/O mode. While rebuilding the spatial index table, the feature class is inaccessible to other users. Note: You can change the grid sizes while the feature class is in normal or load-only I/O mode. If you reset spatial indexes while the feature class is in normal I/O mode, the indexes on the spatial index table are dropped while the spatial index is being re-created.. When the feature class is in normal I/O mode, the envelope is automatically updated whenever a feature that extends the current envelope is added. The envelope is not updated while the feature class is in load-only I/O mode but is recalculated to the full extent when the feature class is reset to normal I/O mode.These examples show the parcels feature class being moved into load only mode then back to normal I/O mode.sdelayer -o load_only_io -l victoria,parcels -u av -p mo -i esri_40sdelayer -o normal_io -l victoria,parcels -u av -p mo -i esri_40When the feature class is returned to normal I/O mode, the spatial index table and database indexes are rebuilt. If the operation does not complete successfully for any reason, the feature class is left in load-only I/O mode.When a feature class is in load-only I/O mode, the unique index is removed from the feature class's spatial column. When the index is absent, it is possible to enter nonunique values into the spatial column with an application not created with the ArcSDE C- or Java application programming interface (API). Therefore, no applications besides ArcSDE or applications created with the ArcSDE C- or Java API should ever update the spatial column. Database administrators should be aware of the increased vulnerability of the spatial column when the feature class is in load-only I/O mode.

I know many of my friends have not carefully read the above English explanation, but it doesn't matter. You just need to know that if we use the load_only_io mode, we will delete the spatial index, we can use the normal_io mode to create a spatial index.

Now that we know these two models, we are introducing them in an extended way.

Note: In general, if our business changes, for example, if we insert a record and use the ArcGIS client or related APIs for operations, apart from adding a new record, we also update spatial indexes synchronously. However, if we have batch updates in our business, in addition to changing data tables, we also need to synchronize batch changes to spatial index information, this will have some impact on performance. Then, we can switch the data to the load_only_io mode before batch changes, and then change the data to the normal_io mode after the change business is completely completed, in order to re-build the index.

For element classes with a large amount of data, we can use the sde command to write batch files to batch create spatial indexes for data.

@ Echo OFFpause "press any key to start" sdelayer-o load_only_io-l quxian, shape-I 5151-s 192.168.220.165-u sde-p sdeecho "layer quxian has deleted the spatial index" sdelayer-o normal_io-l quxian, shape-I 5151-s 192.168.220.165-u sde-p sdeecho "layer quxian has created a spatial index "... sdelayer-o load_only_io-l quxian1, shape-I 5151-s 192.168.220.165-u sde-p sdeecho "layer quxian1 has deleted the spatial index" sdelayer-o normal_io-l quxian1, shape-I 5151-s 192.168.220.165-u sde-p sdeecho "layer quxian1 has created a spatial index" pause "press any key to end"

Save the preceding data as a. bat file and run it directly.

2: Use the python script to recreate the index

If you are aware of a problem, although the sde command can be used to re-index element classes in batches, you still need to specify a specific element class name, if there are even hundreds of element classes in a timely manner, it is still a nightmare for users, so we can easily implement this function using python scripts.

We can use a Python script to traverse a dataset or element class connected to an SDE, and then use the GP Tool to traverse the element classes. The GP Tool can delete and recreate spatial indexes, that's simple.

from arcpy import *env.workspace=r'Database Connections\Connection to 192.168.100.111.sde'        for dataset in ListDatasets():    for fc in ListFeatureClasses("","ALL",dataset):        RemoveSpatialIndex_management(fc)        AddSpatialIndex_management(fc)

During execution, we can see the execution process in the information box on the right.

Bytes -------------------------------------------------------------------------------------------------------

Bytes -------------------------------------------------------------------------------------------------------