How to Keep Your Oracle Spatial Indexes Healthy

When dealing with spatial data and Oracle database, now and then it is recommended to check the status of your spatial indexes.

If not earlier, you’ll be forced to dive into the world of spatial indexes when the spatial errors start showing up, or when spatial performances start to slow down.

Managing Tools When Working with Oracle Database

The first thing you need before you start managing Oracle spatial metadata is a tool to connect to the Oracle database, like SQL*Plus or Toad for Oracle. While there exist a free version of the Toad called Toad for Oracle Freeware, I recommend using an Oracle SQL Developer tool developed by Oracle itself, and it is free.

Oracle SQL Developer offers PL/SQL applications development environment, a worksheet for running queries and scripts, a DBA console for managing the database; a reports interface, a data modeling solution, and a migration platform for moving third- party databases to Oracle.

However, the true power in managing spatial metadata with an Oracle SQL developer comes from a GeoRaptor extension. GeoRaptor is an Open Source project that extends Oracle SQL Developer with additional functionality for database administrators or developers. It is focused on dealing with Oracle Spatial data, it enables visualization of Oracle Spatial data, assists in metadata and spatial index management, and provides a Shapefile importer.

Managing Oracle Database When Working with Intergraph Geomedia

To be able to work with spatial data stored in Oracle database, GeoMedia is using a separate metadata schema called GDOSYS where it holds all the metadata information for all the schemas that are used with GeoMedia applications. Typically, the GDOSYS schema is created either by using Database Utilities, or manually by executing a SQL script CreateGDOSYS.sql that is delivered in the ..\Program Files\GeoMedia Professional\Scripts directory with every GeoMedia installation.

Intergraph is kind enough to provide a free collection of packages and scripts that help in the maintenance of the Oracle spatial/locator schemas used by GeoMedia applications called GeoMedia Oracle Object Model Packages and Scripts (GOOM) (* login is required), which is created and maintained by Chuck Woodbury. GOOM package collection also includes a quick reference guide ‘Using GOOM - Managing Spatial Data for GeoMedia’ that contains GOOM package installation instructions, basic steps required to configure a spatial schema and instruction how to create a GDOSYS schema for use with GeoMedia. It is important to note that the scripts in GOOM package are not officially sanctioned by Intergraph.

Managing Oracle Database Spatial Metadata

Now that we have all the tools we need, we can start managing our spatial metadata.

From my experience, the three most common problems with tables that have spatial data are:

• Table with spatial data does not have an entry in the USER_SDO_GEOM_METADATA view
• Table with spatial data is missing spatial indexes
• Spatial index on the table with spatial data is invalid

Stipica Pavicic shared on his blog SQL statements that help to detect those errors in spatial metadata:

Select that will return all the tables with spatial data that don’t have entry in the USER_SDO_METADATA view

SELECT GEOMET.SDO_OWNER, GEOMET.SDO_TABLE_NAME, GEOMET.SDO_COLUMN_NAME, ATBL.OWNER, ATBL.TABLE_NAME, ATBL.COLUMN_NAME
FROM MDSYS.SDO_GEOM_METADATA_TABLE GEOMET, ALL_TAB_COLS ATBL
WHERE GEOMET.SDO_OWNER       (+)  = ATBL.OWNER
 AND GEOMET.SDO_TABLE_NAME  (+)  = ATBL.TABLE_NAME
 AND GEOMET.SDO_COLUMN_NAME (+)  = ATBL.COLUMN_NAME
 AND GEOMET.SDO_OWNER              IS NULL
 AND GEOMET.SDO_TABLE_NAME         IS NULL
 AND GEOMET.SDO_COLUMN_NAME        IS NULL
 AND ATBL.DATA_TYPE_OWNER      =  'MDSYS'
 AND ATBL.DATA_TYPE            =  'SDO_GEOMETRY'
 AND ATBL.OWNER                != 'MDSYS';

Select that will return all the tables with spatial data that are missing spatial indexes

SELECT ALIC.INDEX_OWNER, ALIC.TABLE_NAME, ALIC.COLUMN_NAME, ATBL.OWNER, ATBL.TABLE_NAME, ATBL.COLUMN_NAME
FROM  ALL_IND_COLUMNS ALIC, ALL_TAB_COLS ATBL
 WHERE ALIC.INDEX_OWNER (+) = ATBL.OWNER
 AND   ALIC.TABLE_NAME  (+) = ATBL.TABLE_NAME
 AND   ALIC.COLUMN_NAME (+) = ATBL.COLUMN_NAME
 AND   ALIC.INDEX_OWNER        IS NULL
 AND   ALIC.TABLE_NAME         IS NULL
 AND   ALIC.COLUMN_NAME        IS NULL
 AND   ATBL.DATA_TYPE_OWNER =  'MDSYS'
 AND   ATBL.DATA_TYPE       =  'SDO_GEOMETRY'
 AND   ATBL.OWNER          !=  'MDSYS';

Select that will return all the invalid spatial indexes

SELECT ALIN.OWNER, ALIN.TABLE_NAME
  FROM ALL_INDEXES ALIN
 WHERE (    ALIN.INDEX_TYPE = 'DOMAIN'
        AND ALIN.ITYP_NAME = 'SPATIAL_INDEX'
        AND ALIN.TABLE_NAME NOT LIKE '%$%'
        AND ALIN.STATUS != 'VALID')
       OR (    ALIN.INDEX_TYPE = 'DOMAIN'
           AND ALIN.ITYP_NAME = 'SPATIAL_INDEX'
           AND ALIN.TABLE_NAME NOT LIKE '%$%'
           AND ALIN.DOMIDX_STATUS != 'VALID')
       OR (    ALIN.INDEX_TYPE = 'DOMAIN'
           AND ALIN.ITYP_NAME = 'SPATIAL_INDEX'
           AND ALIN.TABLE_NAME NOT LIKE '%$%'
           AND ALIN.DOMIDX_OPSTATUS != 'VALID');

Managing Oracle Database Spatial Filter Performance

As the DML operations are performed on a table, the RTree indexes on the geometry fields will degrade, and the performance of spatial filters will begin to suffer. Eventually, the spatial filters will stop working at all. To maintain the spatial filter performance, the RTree indexes have to be regenerated on a regular basis. How often this is necessary depends on the amount of editing that takes place. There is no good rule of thumb here.

Most sites regenerate the spatial indexes every weekend, which is usually sufficient. This regeneration can be executed by setting up an Oracle job that runs the GOOM package procedure SpatialIndexAll.

For example, to quickly rebuild all spatial indexes and fix minor problems with GOOM, you can use the following commands:

EXEC GOOM.DelSidx;
EXEC GOOM.SpatialIndexAll;

Alternatively:

EXEC GOOM.AutoTune;

The first command deletes all the existing spatial indexes and the second recreates them back by using RTrees. The AutoTune command will automate the entire process by first deleting all the existing spatial indexes, then will set the extents, and in the end, will re-index all the data using RTrees.

If you are not using GeoMedia, or you don’t want to use GOOM package, you’ll have to write your SQL scripts that will regenerate spatial indexes.

Managing GDOSYS Metadata Schema

The MODIFICATIONLOG table in GDOSYS can also have a negative impact on performance. If your connection performance seems to be slowing down, it is usually due to the size of this table. To solve this issue, the MODIFICATIONLOG table should be truncated whenever there is a downtime, such as evenings and weekends. Again, you can use an Oracle job for this. Entries in the MODIFICATIONLOG are based on the MODFIEDTABLES. For the best results, you should truncate the MODIFIEDTABLES table at the same time you truncate the MODIFICATIONLOG table.

For cleaning the database and GDOSYS metadata you can use the following commands:

EXEC GOOM.DeleteOrphanMBR;
EXEC GOOM.ClearModLog;

GDOSYS metadata is maintained by a trigger called DELETEMETADATAGMT. If you drop a table, the associated metadata in GDOSYS is also removed for that table. However, this is not true of Oracle’s metadata. If you drop a table using GeoMedia’s tools, the Oracle metadata is also dropped, but if you drop the table from Oracle, the metadata will be orphaned. This commands will delete any Oracle orphan MBR metadata in the USER_SDO_GEOM_METADATAand then will truncate the tables GDOSYS.MODIFICATIONLOG and GDOSYS.MODIFIEDTABLES. It is recommended to run this every night as part of the standard maintenance.