How To: Set the commit interval when creating an ST_Spatial_Index in Oracle


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.


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

SQL> CREATE INDEX cities_sidx ON us_cities (shape)
2 INDEXTYPE IS sde.st_spatial_index
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.