How To: Alter the Deletes Table IDX1 Index for Versioned Tables

Summary

The Oracle optimizer has evolved over many releases, and ESRI has detected the ability to improve the performance of versioned queries by reordering the attributes for a Deletes table IDX1 index.

By placing the sde_deletes_row_id attribute in the leading position of the index, Oracle’s optimizer selects a more efficient execution plan for the query.

For each table that participates in the versioned database, a Base table, Adds table, and Deletes table exist.

The result set for a versioned query are derived by:

-Determining the state and lineage to query;
-Obtaining all rows from the Base table that are not subsequently deleted in the specified lineage;
-Joining all rows from the Adds table that are not subsequently deleted in the specified lineage.

The Deletes table has an index called: D<sde.table_registry.registration_id>_IDX1.

To leverage the changes made to the version queries and improvements in Oracle 10g's Optimizer, the order of the columns on the IDX1 index should be reversed.

When using Oracle 10g, the column order for the D_IDX index should be:

SDE_DELETES_ROW_ID
SDE_STATE_ID

With releases on older versions of Oracle, this change is not necessary.

This change is applicable to Oracle releases.

9.2: All objects registered as versioned with 9.2 have the D-table index created in the correct order.

Procedure

Attention: This procedure is only applicable if the General Update Patch 2 (or higher) has been applied to ArcSDE 9.1 geodatabase on Oracle.

This procedure is not required for earlier releases.

To detect the IDX1 indexes that are incorrectly defined, a) run the query and b) follow the steps given below, if necessary.

A) Determine the indexes to alter.

-- If Oracle is run having DBA privileges, execute this query:

SELECT index_owner, table_name
FROM dba_ind_columns
WHERE index_name LIKE 'D%_IDX1'
AND column_name = 'SDE_STATE_ID'
AND column_position = 1;

-- If you are a schema owner without DBA privileges, execute this query:

SELECT table_name
FROM user_ind_columns
WHERE index_name LIKE 'D%_IDX1'
AND column_name = 'SDE_STATE_ID'
AND column_position = 1;

If the SQL statement returns rows, the table names returned by the queries will need the indexes dropped and re-created in the correct column order.

B) Recreate the IDX1 index and rebuild the statistics.

Follow the steps given below to recreate the index for each Deletes table.

Attention: Repeat these steps for each Deletes table listed in Step A.

First, drop the existing index:

DROP INDEX D<sde.table_registry.registration_id>_IDX1;
Next, create the IDX1 index with the following syntax:

CREATE INDEX D<sde.table_registry.registration_id>_IDX1 ON D<sde.table_registry.registration_id> (sde_deletes_row_id, sde_state_id) PCTFREE 0 INITRANS 4 TABLESPACE <tablespace_name> NOLOGGING;

Set the storage parameters accordingly or see the Oracle documentation for additional information on the storage clause and appropriate values for the Oracle database.
Finally, build Oracle statistics on the table.

Use ArcCatalog, ArcSDE, or Oracle, to generate statistics on the Deletes table.

Note: With ArcCatalog, statistics can be built on all related tables for the object.

Article ID: 000008691

Software: