English

How To: Optimize Oracle query performance and storage when using the ST_Geometry spatial index

Summary

Instructions provided describe how to improve query performance when using ST_Geometry by reducing the number of index blocks read by each spatial query that uses the spatial index (ST_Spatial_Index). Reducing the logical or physical I/O operations for a Structured Query Language (SQL) statement improves query times and improves resources on the server, providing further scalability.

Procedure

The Oracle command ALTER TABLE <table_name> SHRINK SPACE COMPACT CASCADE reduces the number of blocks required to store a table or index by compacting the data in as few blocks as possible. The command requires that the shrunk segment reside in a tablespace using automatic segment management.

To shrink a spatial index, follow the steps below:

  1. Discover the name of the domain index table that represents the spatial index.

    The example below demonstrates how to obtain the spatial index identifier for a table named 'PARCELS', and how to execute the ALTER TABLE command to shrink the spatial index. The steps should be executed as the table's owner.

    Code:
    SQL> SELECT 'S'||index_id||'_IDX$' AS DOMAIN_INDEX
    FROM sde.st_geometry_index
    WHERE owner = USER
    AND table_name = 'PARCELS';

    DOMAIN_INDEX
    ------------
    S15_IDX$

    SQL> ALTER TABLE s15_idx$ SHRINK SPACE COMPACT CASCADE;

    Table altered.

  2. Check the number of index leaf_blocks that existed before and after executing the shrink command. This will help to validate the benefits of using the shrink command.

    For example:

    Before executing the shrink:

    Code:
    SQL> SELECT index_name, leaf_blocks
    FROM user_indexes
    WHERE table_name = 'S15_IDX$';

    INDEX_NAME LEAF_BLOCKS
    -------------------- -----------
    S15$_IX2 18427
    S15$_IX1 27465

    After executing the shrink:

    Code:
    SQL> SELECT index_name, leaf_blocks
    FROM user_indexes
    WHERE table_name = 'S15_IDX$';

    INDEX_NAME LEAF_BLOCKS
    -------------------- -----------
    S15$_IX1 13973
    S15$_IX2 17406

  3. Update the table's statistics after shrinking the spatial index.

    Code:
    SQL> exec dbms_stats.gather_table_stats(user,'parcels');