Forum Stats

  • 3,825,882 Users
  • 2,260,571 Discussions
  • 7,896,720 Comments

Discussions

Oracle Spatial index creation and index options

Laury
Laury Member Posts: 1,665 Silver Badge

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.

Best Answer

  • Hviehman-Oracle
    Hviehman-Oracle Member Posts: 8 Employee
    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.

Answers

  • Hviehman-Oracle
    Hviehman-Oracle Member Posts: 8 Employee
    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.