Top 5 Recent Articles

ARTICLES CATEGORIES

Changing Oracle Spatial Index Parameters on existing index

I received a question from a respected colleague:

Can the parameters of a spatial index be altered? I created several dozen spatial indexes with a layer_gtype of polygon, I now have a requirement to modify them to be multipolygon….

My colleague wishes to do this without dropping the existing index.

The answer to this is yes and the following shows how.

We can generate ALTER INDEX statements by finding all spatial indexes with the layer_gtype of POLYGON and changing the layer_gtype parameter to MULTIPOLYGON as follows.

 SELECT 'ALTER INDEX ' || index_name || ' PARAMETERS(''' ||
        REPLACE(REPLACE(parameters,'=polygon','=POLYGON'),'=POLYGON','=MULTIPOLYGON') || ''');' ||
        CHR(13) ||
        'ALTER INDEX ' || index_name || ' REBUILD;'  AS index_statements
   FROM USER_INDEXES i
  WHERE i.index_name NOT LIKE 'SYS%'
    AND i.ityp_name = 'SPATIAL_INDEX'
    AND ( parameters IS NOT NULL
      AND parameters LIKE '%=poly%'
       OR parameters LIKE '%=POLY%' );
 --
 -- Alternate SQL using Regular Expressions
 --
 SELECT 'ALTER INDEX ' || index_name || ' PARAMETERS(''' ||
        regexp_replace(parameters,'=polygon','=MULTIPOLYGON',1,1,'i') || ''');' ||
        CHR(13) ||
        'ALTER INDEX ' || index_name || ' REBUILD;' AS index_statements
   FROM USER_INDEXES i
  WHERE i.index_name NOT LIKE 'SYS%'
    AND i.ityp_name = 'SPATIAL_INDEX'
    AND regexp_like(parameters,'=polygon','i');
 --
 -- Results
 --
 INDEX_STATEMENTS
 ---------------------------------------------------------------------------------------------------------------------------------------
 ALTER INDEX PROJPOLY3D_GEOM_$X PARAMETERS('sdo_indx_dims=2, layer_gtype=MULTIPOLYGON, sdo_non_leaf_tbl=true, sdo_rtr_pctfree=1');
 ALTER INDEX PROJPOLY3D_GEOM_$X REBUILD;
 ALTER INDEX PROJ41014POLY2D_GEOM_SPIX PARAMETERS('sdo_indx_dims=2, layer_gtype=MULTIPOLYGON, tablespace=USERS, work_tablespace=USERS');
 ALTER INDEX PROJ41014POLY2D_GEOM_SPIX REBUILD;
 ALTER INDEX PROJ41914POLY2D_GEOM_$X PARAMETERS('sdo_indx_dims=2, layer_gtype=MULTIPOLYGON, sdo_non_leaf_tbl=true, sdo_rtr_pctfree=1');
 ALTER INDEX PROJ41914POLY2D_GEOM_$X REBUILD;
 ALTER INDEX PROJPOLY2D_GEOM_$X PARAMETERS('sdo_indx_dims=2, layer_gtype=MULTIPOLYGON, sdo_non_leaf_tbl=true, sdo_rtr_pctfree=1');
 ALTER INDEX PROJPOLY2D_GEOM_$X REBUILD;

Executing a pair….

 ALTER INDEX PROJPOLY2D_GEOM_$X PARAMETERS('sdo_indx_dims=2, layer_gtype=MULTIPOLYGON, sdo_non_leaf_tbl=true, sdo_rtr_pctfree=1');
 ALTER INDEX PROJPOLY2D_GEOM_$X REBUILD;
 -- Results
 INDEX PROJPOLY2D_GEOM_$X altered.
 INDEX PROJPOLY2D_GEOM_$X altered.

I hope this helps someone out there.