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.