Skip to Main Content
Forums

LiveLabs & Workshops

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Hviehman-Oracle
Answer

Hi Laury,
I have come across your post by coincidence. No one will be looking for questions on Oracle Spatial in this forum. For questions on Oracle Spatial, you should be using the "Oracle Spatial" forum rather than Oracle Graph.
To answer your questions - if you have point data only in your layer, we recommend using the 'layer_gtype=point' option. Response time will be faster because we're using optimizations specific to points in that case.
If you are constantly adding, deleting, or modifying your location information, e.g., in tracking and tracing scenarios where you permanently acquire GPS data, the 'cbtree_index=true' option may be beneficial.
If you'd like to control where your spatial index data is stored, use the tablespace option. This approach can be helpful to avoid contention in your default tablespace.
I hope this helps.
Hans.

Marked as Answer by Laury · Apr 22 2022
1 - 1

Post Details

Added on Jan 29 2022
#index, #oracle-graph, #performance, #spatial
1 comment
435 views