HOW TO

Set the commit interval when creating an ST_Spatial_Index in Oracle

Last Published: April 25, 2020

Summary

ST_Commit_Rows is an optional argument used in the ST_Spatial_Index parameters clause used when creating a spatial index on an ST_Geometry attribute in Oracle.

The ST_Commit_Rows parameter controls the frequency of commits executed, while the ST_Spatial_Index domain index is created. Once the value is reached, a commit is executed and the internal counter, which records the number of rows being inserted into the domain index, is reset to 0. Each time the counter reaches the ST_Commit_Rows value, a commit is executed.

If the argument is not specified in the parameters clause of the CREATE INDEX command, the default value for ST_Commit_Rows is 10000.

Procedure

The example below demonstrates setting the ST_Commit_Rows argument in the parameters clause when creating an ST_Spatial_Index.

Code:
SQL> CREATE INDEX cities_sidx ON us_cities (shape)
2 INDEXTYPE IS sde.st_spatial_index
3 PARAMETERS
4 ('st_grids=1,0,0 st_srid=1 st_commit_rows=25000');

While the index is being created, a commit is executed with every 25,000 rows recorded. The commit ensures that undo resources are released, avoiding errors.

    Article ID:000009576

    Software:
    • Legacy Products

    Receive notifications and find solutions for new or common issues

    Get summarized answers and video solutions from our new AI chatbot.

    Download the Esri Support App

    Discover more on this topic

    Get help from ArcGIS experts

    Contact technical support

    Download the Esri Support App

    Go to download options