FAQ: What Type of DBMS Resources Are Consumed When Creating a Spatial Index in Oracle with

Answer

When creating a spatial index on an st_geometry attribute, either by using the CREATE INDEX statement with SQL or with ArcGIS, many resources in Oracle are consumed and will be impacted based upon the number of rows in the table being indexed.

For example, the following CREATE INDEX statement in SQL*PLUS,

Code:
SQL> CREATE INDEX parcels_shp_idx ON parcels (shape)
  2  INDEXTYPE IS sde.st_spatial_index
  3  PARAMETERS ('st_grids=1000 st_srid=5');

Index created.

will consume memory in the Oracle PGA process to read the rows from the table for the index, perform write operations to create the spatial index, and generate Oracle redo (because of the DML being performed to generate the index values).

The amount of memory, disk i/o operations, and redo generated are dependent on the size of the table being indexed (the number of entries that must be generated in the spatial index).

Unfortunately, Oracle does not support direct writes with Indexed Organized Tables that would significantly reduce the impact on the Oracle buffer cache and redo when creating the spatial index. When performing a direct write, the inserted rows bypass the Oracle buffer cache, and the rows are written directly to the Oracle block on disk and very little redo is generated.

One alternative, when creating a spatial index on very large tables, is to disable Oracle's archive process. Archiving is the process of copying the Oracle redo log file to a backup location on every redo log file switch. If archiving is disabled, the database cannot be recovered if corruption occurs. So, do not disable archiving if the system is in production (users updating and maintaining other tables in the database) when creating the spatial index.

Article ID:000010063

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

What type of DBMS resources are consumed when creating a spatial index in Oracle with st_geometry?

Answer

Discover more on this topic