Spatially cluster the storage of a table using ST_Geometry

Summary

A spatially clustered table is very important for two reasons: to reduce physical and logical I/O and to improve the optimizer's calculated cost or the clustering factor for using the spatial index for Structured Query Language (SQL) statements containing spatial operators.

The optimizer's clustering factor is a number representing the degree in which data is randomly distributed throughout a table in relationship with the values stored within the index.

For example, when performing a spatial search at a given extent such as, a municipal boundary, if each feature is stored in an individual Oracle block, the time to fetch each feature can be substantially more than if all the features resided in the same Oracle block.

Because of the distribution of data among several blocks, the time to fetch the data increases. Potentially, if the clustering factor is extremely poor, meaning very few features that are spatially clustered are physically stored in the same data blocks, the optimizer likely selects a full table scan verses using the spatial index. The reason why it becomes more efficient to perform a full table scan verses using an index is to reduce the number of I/O operations, physical or logical I/O.

For example, if the spatial index was used to retrieve every row from a table, the amount of I/O would be two times or more the number of I/O operations than performing a full table scan. To use an index, Oracle must first read the index blocks, obtain the rowid for the feature to retrieve, and then read the block where the row is physically stored.

Instructions provided describe how to improve performance by reducing the number of I/O operations while at the same time ensuring Oracle is able to use the spatial index efficiently by spatially clustering the table.

Procedure

1. Identify the spatial index parameters for the table and its spatial attribute. The table's ST_Geometry attribute must be spatially indexed or no data will be present in the sde.st_geometry_index table for the table.
   
   

   Code:
   SELECT table_name, column_name, index_name, index_id, grid, srid 
   FROM sde.st_geometry_index
   WHERE table_name = '<table_name>' AND owner = USER;

   
   Use these values in the SQL to create the new table and new spatial index. Be aware of all the existing indexes on any other attributes as the final step is to re-create these indexes.
   
   

   Code:
   SELECT a.index_name, b.uniqueness, a.column_name, a.column_position 
   FROM user_ind_columns a, user_indexes b
   WHERE a.table_name = '<table_name>' and a.index_name = b.index_name 
   ORDER BY a.index_name, a.column_position;

   
   
2. Create the new table that is spatially clustered.
   
   

   Code:
   CREATE TABLE <table_name>_bk AS
     SELECT b.* 
     FROM (SELECT s.sp_id, s.gx, s.gy, 
           ROW_NUMBER() OVER (PARTITION BY s.sp_id ORDER BY s.gx, s.gy) rn 
           FROM s<index_id>_idx$ s) sp, <table_name> b
     WHERE rn = 1 AND b.rowid = sp.sp_id
     ORDER BY sp.gx, sp.gy;

   
   
3. If the previous step was successful, DROP the original table before proceeding. Always ensure the data is correctly backed up and recoverable, if any unforeseen problems are encountered.
   
   

   Code:
   DROP TABLE <table_name>;

   
   The original table must be removed to allow the <table_name>_bk to be renamed to the original table name.
   
   

   Code:
   RENAME <table_name>_bk TO <table_name>;

   
   
4. Create the spatial index and re-create any other indexes that were present on the original table and gather statistics.
   
   Using the values from step 1, create the spatial index with the same parameters.
   
   

   Code:
   CREATE INDEX <index_name> ON <table_name> (<column_name>) INDEXTYPE IS
   sde.st_spatial_index PARAMETERS ('st_grids=<grid1,grid2,grid3> st_srid=<srid>');

   
   If any other indexes were present on the original table, create those indexes.
   
   Gather statistics on the new table and indexes.
   
   

   Code:
   EXEC dbms_stats.gather_table_stats(USER,'<table_name>');

   
   
5. The following is a complete example for spatially clustering a PARCELS table.
   
   

   Code:
   SELECT table_name, column_name, index_name, index_id, grid, srid 
   FROM sde.st_geometry_index
   WHERE table_name = 'PARCELS' AND owner = USER;
   
   SELECT a.index_name, b.uniqueness, a.column_name, a.column_position 
   FROM user_ind_columns a, user_indexes b
   WHERE a.table_name = 'PARCELS' and a.index_name = b.index_name 
   ORDER BY a.index_name, a.column_position;
   
   CREATE TABLE parcels_bk AS
     SELECT b.* 
     FROM (SELECT s.sp_id, s.gx, s.gy, 
           ROW_NUMBER() OVER (PARTITION BY s.sp_id ORDER BY s.gx, s.gy) rn 
           FROM s31_idx$ s) sp, parcels b
     WHERE rn = 1 AND b.rowid = sp.sp_id
     ORDER BY sp.gx, sp.gy;
   
   DROP TABLE parcels;
   
   RENAME TABLE parcels_bk TO parcels;
   
   CREATE INDEX a40_ix1 ON parcels (shape) INDEXTYPE IS
   sde.st_spatial_index PARAMETERS ('st_grids=500,0,0 st_srid=2');
   
   CREATE UNIQUE INDEX r132_sde_rowid_uk ON parcels (objectid);
   
   CREATE INDEX apn_idx ON parcels (apn);
   
   CREATE INDEX owner_idx ON parcels (owner);