Duplicate entries present in the sde table mvtables_modified with Oracle

Description

It is possible to have duplicate entries in the ArcSDE table mvtables_modified if the ArcSDE instance was created prior to the ArcGIS 9.0 release.

The duplicate entries do not cause or lead to any date inconsistencies but may potentially impact performance if the table becomes extremely large.

Duplicates can be detected by executing the following query as the SDE user (this example demonstrates the SQL syntax with Oracle using SQL*Plus),

Code:
SQL> SELECT COUNT(*) FROM sde.mvtables_modified
GROUP BY registration_id, state_id
HAVING COUNT(*) > 1;

Cause

Duplicates are present in the mvtables_modified table because there is not a unique index or constraint preventing duplicate values from being inserted.

The ArcGIS 9.0 release started placing a unique index on the table to prevent duplicate entries from being inserted. The new unique index was only applied to new geodatabases; upgrading a geodatabase to ArcGIS 9.0 or later would not add the unique index (because duplicate values may have existed). In addition to the unique index, the geodatabase now caches information about the versioned tables that have been modified in an edit operation and no longer attempts to perform multiple inserts into mvtables_modified as the versioned class is edited.

Another example of when duplicate entries can be introduced is when editing multi-version views. Each SQL statement that performs an insert, update, or delete against a multi-version view will trigger an insert statement to write the view's registration_id to mvtables_modified. In the case where no unique index is present, this can quickly lead to hundreds or thousands of entries being created. For very active applications that frequently modify multi-version views, this can lead to the mvtables_modified table becoming extremely large and potentially impacting ArcGIS performance.

Solution or Workaround

Note:
One should only add the unique index if the current ArcSDE instance is 9.0 or later and is Oracle.

1. Identify if there is already a unique index present on the table. If yes, there is no need to perform any additional steps.
   
   As the SDE user in SQL*Plus execute the following SQL statement,
   
   

   Code:
   SQL> SELECT index_name, uniqueness FROM user_indexes
   WHERE table_name = 'MVTABLES_MODIFIED';

   
   
   If the index is non-unique, then proceed to the next steps.
2. Recreate the table and create a unique index. First, rename the table because the next step will recreate the table using a CREATE TABLE AS SELECT statement.
   
   

   Code:
   SQL> RENAME mvtables_modified TO mvtables_modified_backup;

   
   
   Next, recreate the mvtables_modified table.
   
   

   Code:
   SQL> CREATE TABLE mvtables_modified AS
   SELECT DISTINCT state_id, registration_id
   FROM mvtables_modified_backup
   ORDER BY state_id;

   
   
   Next, create the unique index on the table to ensure duplicates cannot be inserted.
   

   Code:
   SQL> CREATE UNIQUE INDEX mvtables_moidified_ix ON sde.mvtables_modified (state_id, registration_id);

   
   
   If necessary, specify the additional storage clause arguments for defining segment sizes and tablespace.
3. Grant the required privileges on the table, drop the original table, and gather table statistics.
   
   

   Code:
   SQL> GRANT SELECT ON mvtables_modified TO PUBLIC;
   
   SQL> DROP TABLE mvtables_modified_backup;
   
   SQL> EXEC dbms_stats.gather_table_stats(user,'mvtables_modified');