Go to content Go to navigation and search

Home

Current Oracle Spatial Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Changing Oracle Spatial Index Parameters on existing index

Wednesday October 19 2011 at 11:21

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.

  1. SELECT 'ALTER INDEX ' || index_name || ' PARAMETERS(''' ||
  2.        REPLACE(REPLACE(parameters,'=polygon','=POLYGON'),'=POLYGON','=MULTIPOLYGON') || ''');' ||
  3.        CHR(13) ||
  4.        'ALTER INDEX ' || index_name || ' REBUILD;'  AS index_statements
  5.   FROM USER_INDEXES i
  6.  WHERE i.index_name NOT LIKE 'SYS%'
  7.    AND i.ityp_name = 'SPATIAL_INDEX'
  8.    AND ( parameters IS NOT NULL
  9.      AND parameters LIKE '%=poly%'
  10.       OR parameters LIKE '%=POLY%' );
  11. --
  12. -- Alternate SQL using Regular Expressions
  13. --
  14. SELECT 'ALTER INDEX ' || index_name || ' PARAMETERS(''' ||
  15.        regexp_replace(parameters,'=polygon','=MULTIPOLYGON',1,1,'i') || ''');' ||
  16.        CHR(13) ||
  17.        'ALTER INDEX ' || index_name || ' REBUILD;' AS index_statements
  18.   FROM USER_INDEXES i
  19.  WHERE i.index_name NOT LIKE 'SYS%'
  20.    AND i.ityp_name = 'SPATIAL_INDEX'
  21.    AND regexp_like(parameters,'=polygon','i');
  22. --
  23. -- Results
  24. --
  25. INDEX_STATEMENTS
  26. ---------------------------------------------------------------------------------------------------------------------------------------
  27. ALTER INDEX PROJPOLY3D_GEOM_$X PARAMETERS('sdo_indx_dims=2, layer_gtype=MULTIPOLYGON, sdo_non_leaf_tbl=true, sdo_rtr_pctfree=1');
  28. ALTER INDEX PROJPOLY3D_GEOM_$X REBUILD;
  29. ALTER INDEX PROJ41014POLY2D_GEOM_SPIX PARAMETERS('sdo_indx_dims=2, layer_gtype=MULTIPOLYGON, tablespace=USERS, work_tablespace=USERS');
  30. ALTER INDEX PROJ41014POLY2D_GEOM_SPIX REBUILD;
  31. ALTER INDEX PROJ41914POLY2D_GEOM_$X PARAMETERS('sdo_indx_dims=2, layer_gtype=MULTIPOLYGON, sdo_non_leaf_tbl=true, sdo_rtr_pctfree=1');
  32. ALTER INDEX PROJ41914POLY2D_GEOM_$X REBUILD;
  33. ALTER INDEX PROJPOLY2D_GEOM_$X PARAMETERS('sdo_indx_dims=2, layer_gtype=MULTIPOLYGON, sdo_non_leaf_tbl=true, sdo_rtr_pctfree=1');
  34. ALTER INDEX PROJPOLY2D_GEOM_$X REBUILD;

Executing a pair….

  1. ALTER INDEX PROJPOLY2D_GEOM_$X PARAMETERS('sdo_indx_dims=2, layer_gtype=MULTIPOLYGON, sdo_non_leaf_tbl=true, sdo_rtr_pctfree=1');
  2. ALTER INDEX PROJPOLY2D_GEOM_$X REBUILD;
  3. -- Results
  4. INDEX PROJPOLY2D_GEOM_$X altered.
  5. INDEX PROJPOLY2D_GEOM_$X altered.
I hope this helps someone out there.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [1]

Great example of catering to changing requirements of data. Thanks for the posting.

— Jamie · 2 November 2011, 09:04 · #