Alter the indexes on the Adds table for Oracle 10g

Summary

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:

Note:
 Perform this procedure on all versioned objects in the geodatabase.

Procedure

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:
   
   

   Code:
   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:
   
   

   Code:
   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:
   
   

   Code:
   ALTER TABLE a<#> DROP CONSTRAINT a<#>_pk;
   
   DROP INDEX a<#>_rowid_ix1;

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

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

   
   
   

   Note:
    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:

   
   
   

   Code:
   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:
   
   

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