English

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

Question

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.

Related Information