FAQ: Can Different St_grid Values Be Specified When Creating a Spatial Index in Oracle on

Answer

When creating a local spatial index, st_geometry_index, on a partitioned table in Oracle, the parameter clause arguments for st_grids and st_srid must use the same value. It is not valid to mix grid sizes or spatial references between partitions.

The following example demonstrates the invalid usage of the parameters clause, because different grid sizes are specified for each partition's local spatial index.

Code:
CREATE INDEX st_shape_index
  ON parcels (shape)
  INDEXTYPE IS sde.st_spatial_index
  LOCAL (PARTITION quarter1 PARAMETERS('st_srid=1 st_grids=100 TABLESPACE q1_idx'),
         PARTITION quarter2 PARAMETERS('st_srid=1 st_grids=280 TABLESPACE q2_idx'),
         PARTITION quarter3 PARAMETERS('st_srid=1 st_grids=400 TABLESPACE q3_idx'),
         PARTITION quarter4 PARAMETERS('st_srid=1 st_grids=300 TABLESPACE q4_idx'),
         PARTITION qunknown PARAMETERS('st_srid=1 st_grids=100 TABLESPACE qunknown_idx'))

The proper SQL syntax for creating the spatial index is to set the same values for the grid sizes and spatial reference.

Code:
CREATE INDEX st_shape_index
  ON parcels (shape)
  INDEXTYPE IS sde.st_spatial_index
  LOCAL (PARTITION quarter1 PARAMETERS('st_srid=1 st_grids=100 TABLESPACE q1_idx'),
         PARTITION quarter2 PARAMETERS('st_srid=1 st_grids=100 TABLESPACE q2_idx'),
         PARTITION quarter3 PARAMETERS('st_srid=1 st_grids=100 TABLESPACE q3_idx'),
         PARTITION quarter4 PARAMETERS('st_srid=1 st_grids=100 TABLESPACE q4_idx'),
         PARTITION qunknown PARAMETERS('st_srid=1 st_grids=100 TABLESPACE qunknown_idx'))

The storage clause for each local spatial index can be different.

Article ID: 000010815

Software:

Legacy Products

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Can different st_grid values be specified when creating a spatial index in Oracle on a partitioned table?

Answer

Receive notifications and find solutions for new or common issues

Discover more on this topic