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

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');