Oracle Spatial index

Spatial index creation

-- Create the index without any parameters

CREATE INDEX geod_counties_sidx ON geod_counties(geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX

The following parameters are recommended when creating spatial indexes (ie: whihc is a R-Tree index).

CREATE INDEX sp_idx ON my_table (location)
INDEXTYPE IS mdsys.spatial_index
PARAMETERS ('tablesapce=tb_name work_tablespace=work_tb_name')

• WORK_TABLESPACE - During spatial index creation, the process creates intermediate
tables that get dropped when the index is complete. The intermediate tables can take up to 2 times the size of the final index. If WORK_TABLESPACE is not specified, the
intermediate tables are created in the same tablespace as the final index, causing
fragmentation, and possible performance degradation.
You can use SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE, and multiply the result by
2 to provide guidance on sizing the work tablespace. The work tablespace can be re-used to create other spatial indexes.

• LAYER_GTYPE – This parameter is needed especially when working with point-only
layers. If a point-only layer stores its points in the SDO_ORDINATE_ARRAY, you can
still specify LAYER_GTYPE=POINT on spatial index creation. This can help query
performance when performing spatial analysis.

• SDO_NON_LEAF_TBL – This parameter is useful for very large spatial indexes (not
necessary for smaller spatial indexes). This generates two spatial index tables instead of one.The smaller spatial index table is the non-leaf table, which is traversed most often during spatial analysis. It can be beneficial to pin the non-leaf table into the buffer pool, since it is accessed most often. See the example below.

-- Create the index
CREATE INDEX geod_counties_sidx ON geod_counties(geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('sdo_non_leaf_tbl=TRUE');

-- Find the non leaf index table name
SELECT sdo_nl_index_table FROM user_sdo_index_metadata
WHERE sdo_index_name='GEOD_COUNTIES_SIDX';


Pin the table in memory
ALTER TABLE MDNT_A930$ STORAGE(BUFFER_POOL KEEP);