Skip to Main Content

Oracle Spatial index creation and index options

LauryJan 29 2022

Hi,
I am working with Oracle RDMS 19c and Oracle Spatial.
I have a table that contains point-layer geometries.
The column "location" of the table "customers" is od SDO_GEOMETRY type.
I need to create a Sptial index on that column.
What should be the best approach to create such an index id that table only contains point-layer geometries?:
1)
CREATE INDEX customers_sidx ON customers(location)
INDEXTYPE IS mdsys.spatial_index_v2
PARAMETERS ('layer_gtype=point cbtree_index=true');
=> the default tablespace is used
or:
2)
CREATE INDEX customers_sidx
ON customers(location)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS ('layer_gtype=point tablespace=CUSTOMER_POINT_D');
=> a dedicated tablespace is used (CUSTOMER_POINT_D)
or:
3)
CREATE INDEX customers_sidx
ON customers(location)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS ('layer_gtype=point cbtree_index=true tablespace=CUSTOMER_POINT_D')
=> a dedicated tablespace is used (CUSTOMER_POINT_D)

What approach to consider if the table will contain other layer types?
Thanks by advance.
Kind Regards.

This post has been answered by Hviehman-Oracle on Apr 4 2022
Jump to Answer
Comments
Post Details
Added on Jan 29 2022
1 comment
26 views