FAQ: How is a Partitioned Spatial Index on a Partition Table Created in Oracle?

Answer

An ST_Geometry st_spatial_index domain index can be built to have discrete index partitions that correspond to each partition for range-partitioned tables. This type of index is called a local domain index, as opposed to a global domain index, which has no index partitions.

A local domain index is equally partitioned with the underlying table. For each partition: all keys in a local domain index refer to rows stored in its corresponding table partition.

St_spatial_index local domain indexes can be created only for range-partitioned, heap-organized tables. Local domain indexes cannot be built for hash-partitioned tables or Indexed Organized Tables.

The CREATE INDEX statement creates and partitions the index:

CREATE INDEX [schema.]index
ON [schema.]table [t.alias] (indexed_column)
INDEXTYPE IS sde.st_spatial_index [LOCAL [PARTITION [partition [PARAMETERS ('string')]]] [...] ]
[PARALLEL parallel_degree]
[PARAMETERS ('string')];

The following example assumes that the parcels table is partitioned. The CREATE INDEX statement creates a local st_spatial_index for each partition using the same parameters and default storage settings for each index.

Code:
SQL> CREATE INDEX st_shape_index
  2  ON parcels (shape)
  3  INDEXTYPE IS sde.st_spatial_index
  4  PARAMETERS('st_srid=1 st_grids=280') 
  5  LOCAL;

Index created.

The next example demonstrates how to create a local st_spatial_index domain index for each partition and setting different parameter values, such as grid size, tablespace and storage parameters, for each local index that is created.

Code:
SQL> CREATE INDEX st_shape_index
  2  ON parcels (shape)
  3  INDEXTYPE IS sde.st_spatial_index
  4  LOCAL (PARTITION quarter1 PARAMETERS('st_srid=1 st_grids=280 TABLESPACE q1_idx 
  5           STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0)'),
  6         PARTITION quarter2 PARAMETERS('st_srid=1 st_grids=280 TABLESPACE q2_idx 
  7           STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0)'),
  8         PARTITION quarter3 PARAMETERS('st_srid=1 st_grids=280 TABLESPACE q3_idx 
  9           STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0)'),
 10         PARTITION quarter4 PARAMETERS('st_srid=1 st_grids=280 TABLESPACE q4_idx 
 11           STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0)'),
 12         PARTITION qunknown PARAMETERS('st_srid=1 st_grids=280 TABLESPACE qunknown_idx 
 13           STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0)'));

Index created.

Note:
The spatial reference st_srid value must be the same for every partition.

For additional information on partitioning see the link to Oracle's documentation in Related Information.

Article ID:000009709

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

How is a partitioned spatial index on a partition table created in Oracle?

Answer

Related Information

Discover more on this topic