FAQ: What Indexes in Oracle Should Be Frequently Rebuilt to Maintain Optimal Performance w

Answer

A versioned query's performance in Oracle is highly dependent on the table's delta table indexes. The version query performs multiple joins and applies several predicate filters that will leverage the table's indexes.

The version query's performance can be impacted if the indexes used in the access path must read or scan more index blocks then necessary. The fewer number of blocks read; the better the performance.

To maintain the highest level of performance and response time for a version query, it is strongly recommended that the delta table indexes are rebuilt on a regular basis to ensure the indexes are as packed with the least number of index blocks as possible. Rebuilding the indexes re-creates the index with just the number of blocks required. Over time the index may grow by adding index leaf blocks, as new values are inserted into the table.

The following indexes on the versioned table's adds and deletes table are the indexes that should be rebuilt.

Indexes on the adds table:
A<#>_PK
A<#>_STATEID_IX1

Indexes on the deletes table:
D<#>_PK
D<#>_IDX1

Where <#> equals the registration_id value from the sde.table_registry table for the given versioned feature class.

To obtain the registration_id value for a versioned table, execute the following query in SQL*Plus:

Code:
SQL> SELECT registration_id
  2  FROM sde.table_registry
  3  WHERE table_name = 'SEWERS'
  4  AND owner = 'TOM';

REGISTRATION_ID
---------------
             51

To rebuild the index execute the ALTER INDEX REBUILD command.

Code:
SQL> ALTER INDEX a51_pk REBUILD;

Index altered.

Repeat the command for each of the four important delta table indexes.

The frequency for which one should rebuild the indexes is completely dependent on the volume of edits occurring each day for the versioned table. If the class is not being edited, then there is no need to rebuild the indexes.

Article ID: 000010245

Software:

Legacy Products

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

What indexes in Oracle should be frequently rebuilt to maintain optimal performance with versioned tables?

Answer

Receive notifications and find solutions for new or common issues

Discover more on this topic