How To: Alter the indexes on the Adds table for Oracle 10g


Objects that are registered as versioned have supporting Adds and Deletes tables to maintain inserted, updated, and deleted records. The Adds and Deletes tables are highly active, meaning they experience constant change and ESRI has determined, for performance reasons, the following changes should be made to the Adds table indexes:

· Flip the order of the A<#>_PK index
· Drop the A<#>_ROWID_IX1 index
· Create an index on the SDE_STATE_ID column

This change is applicable to all Oracle releases.

Perform this procedure on all versioned objects in the geodatabase.


For every versioned object, perform the following steps:

  1. Determine the indexes to be altered
    First determine which Adds table indexes need to be altered.

    As the DBA
    To locate all the indexes on the Adds tables, run the following query as a user with DBA privileges:

    SELECT index_name, index_owner, table_name, column_name, column_position
    FROM dba_ind_columns
    WHERE index_owner = '<owner>'
    AND index_name like 'A%'
    AND column_name <> 'SHAPE'
    ORDER BY 1, 5;

    OR, as the data owner
    To determine all the indexes on the schema's Adds tables, run the following query:

    SELECT index_name, table_name, column_name, column_position
    FROM user_ind_columns
    WHERE index_name like 'A%'
    AND column_name <> 'SHAPE'
    ORDER BY 1,4;

    The output of either of these commands lists all the indexes created on the Adds tables. Notice that the A<#>_PK index currently defined in the following column order:
    1 - SDE_STATE_ID
    2 - OBJECTID

    In the next steps, drop the current A<#>_PK and ROWID_IX1 indexes, create the A<#>_PK index in the reverse order, and create an index on the SDE_STATE_ID column.
  2. Drop the A<#>_PK and ROWID_IX1 indexes
    Log in to SQL*Plus as the owner of the versioned object(s). For each Adds table, drop the A<#>_PK and A<#>_ROWID_IX1 indexes:


    DROP INDEX a<#>_rowid_ix1;

  3. Create the A<#>PK constraint and index
    Next, create the A<#>_PK index with the following command:

    ALTER TABLE a<#>
    ADD CONSTRAINT a<#>_pk PRIMARY KEY (objectid, sde_state_id);

    The sde_row_id may not necessarily be named ObjectID, in some cases it is named oid or even a user defined name. Ensure the correct column is used as the sde_row_id attribute. One can verify the correct column with the following SQL:

    SELECT rowid_column FROM sde.table_registry WHERE owner = USER and table_name = 'table_wanted';

  4. Create an index on the SDE_STATE_ID column
    Use the following command to create a new index on the SDE_STATE_ID column:

    CREATE INDEX a<#>_state_ix1 ON a<#> (sde_state_id);

Related Information