English

How To: Improve ArcSDE/Oracle multi-version query performance

Summary

For each table that participates in the versioned database, a Base table, Adds Table, and Deletes table can be found.

The records for a specific version are derived by:

• Determining which database state to which the desired version points.

• Selecting all records from the Base table that are not subsequently deleted in the lineage of the database state.

• Adding all records from the Adds table that are not subsequently deleted in the lineage of the database state.

The Deletes table has a primary key: D<sde.table_registry.registration_id>_PK. The ArcSDE/Oracle multi-version query relies heavily on this index being correct.

If the primary key index has been created in the incorrect order, Oracle is forced to perform more work to execute the query.

The correct column order is:

DELETED_AT
SDE_DELETES_ROW_ID
SDE_STATE_ID

This order was flipped with earlier releases of ArcSDE.

Procedure

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

Running the query:

• If Oracle is being run with DBA privileges, execute this query:

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


• If the user is a schema owner without DBA privileges, execute this query:

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



If the SQL statement returns rows, the table names returned by the queries must have the primary key indexes dropped and re-created in the correct column order. Follow the steps given below:

  1. Disable and drop the existing index:

    Code:
    ALTER TABLE D<sde.table_registry.registration_id> DISABLE CONSTRAINT D<sde.table_registry.registration_id>_PK;


    Code:
    ALTER TABLE D<sde.table_registry.registration_id> DROP CONSTRAINT D<sde.table_registry.registration_id>_PK;

  2. Re-create the primary key index with the following syntax:

    Code:
    ALTER TABLE D<sde.table_registry.registration_id> ADD CONSTRAINT D<sde.table_registry.registration_id>_PK PRIMARY KEY (deleted_at, sde_deletes_row_id, sde_state_id) USING INDEX PCTFREE 10 INITRANS 2 STORAGE (FREELISTS 4 MINEXTENTS 1 PCTINCREASE 0) TABLESPACE <tablespace_name>;


    Set the storage parameters accordingly or see the Oracle documentation for additional information on the storage clause and appropriate values for the Oracle database.

    This change is applicable to all releases of Oracle.