Knowledge Base - Technical Articles
HowTo: Spatially cluster the storage of a table using ST_Geometry
| Article ID: | 32423 |
|---|---|
| Software: | ArcGIS - ArcInfo 9.2, 9.3, 9.3.1 ArcSDE 9.2, 9.3, 9.3.1 |
| Platforms: | N/A |
Summary
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
- 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.
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.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;
- Create the new table that is spatially clustered.
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;
- 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.
DROP TABLE <table_name>;
The original table must be removed to allow the <table_name>_bk to be renamed to the original table name.RENAME <table_name>_bk TO <table_name>;
- 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.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.EXEC dbms_stats.gather_table_stats(USER,'<table_name>');
- The following is a complete example for spatially clustering a PARCELS table.
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);
Created: 1/5/2007
Last Modified: 5/3/2011
(2)
Comments
By Anonymous - 04/26/2010 11:55 AM
The article needs to be updated.
-- ESRI arcticle 32423: Spatially cluster the storage of a table using ST_Geometry -- -- Stored proc: LAYER_OPTIMIZER -- Purpose: Arcticles code samples packaged up as a stored procedure -- Note: Does not support non spatial concatenated indexes -- Support: Free to use - no support -- Tor Nielsen, IHS, Denver, CO USA - April 26, 2010 -- create or replace PROCEDURE LAYER_OPTIMIZER(layer_name IN VARCHAR2 ) AS BEGIN DECLARE CURSOR cur_get_spatial_index IS
Rating:
By Anonymous - 12/07/2007 4:17 PM
The article contains a typographical or grammatical error.
Please correct the SQL syntax as displayed below.
Incorrect Syntax in Step 4:
==========================
CREATE INDEX
By Anonymous - 02/01/2011 1:18 PM
I have suggested related resources/links that can enhance this article. See below.
I hate high Oracle clustering factor
Rating:
