PROBLEM
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;
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.
Note:
One should only add the unique index if the current ArcSDE instance is 9.0 or later and is Oracle.
Code:
SQL> SELECT index_name, uniqueness FROM user_indexes
WHERE table_name = 'MVTABLES_MODIFIED';
Code:
SQL> RENAME mvtables_modified TO mvtables_modified_backup;
Code:
SQL> CREATE TABLE mvtables_modified AS
SELECT DISTINCT state_id, registration_id
FROM mvtables_modified_backup
ORDER BY state_id;
Code:
SQL> CREATE UNIQUE INDEX mvtables_moidified_ix ON sde.mvtables_modified (state_id, registration_id);
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');
Article ID:000010828
Get help from ArcGIS experts
Download the Esri Support App