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.
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.
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.
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
- 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
SELECT GEOMET.SDO_OWNER, GEOMET.SDO_TABLE_NAME, GEOMET.SDO_COLUMN_NAME, ATBL.OWNER, ATBL.TABLE_NAME, ATBL.COLUMN_NAME
SELECT ALIC.INDEX_OWNER, ALIC.TABLE_NAME, ALIC.COLUMN_NAME, ATBL.OWNER, ATBL.TABLE_NAME, ATBL.COLUMN_NAME
SELECT ALIN.OWNER, ALIN.TABLE_NAME
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:
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.
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
For cleaning the database and GDOSYS metadata you can use the following commands:
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.MODIFIEDTABLES. It is recommended to run this every night as part of the standard maintenance.