Why Is the Number of Indexes Different After Oracle Data is Migrated to GaussDB?
Symptom
After data is migrated from Oracle to GaussDB, the total number of indexes is different in the source and target databases.
Causes
During UGO collection, the primary key and unique constraints, and indexes generated by the system are filtered out.
Solution
Connect to the Oracle database and run the following SQL statements to query the number of indexes:
SELECT count(*) FROM (select owner, index_name, status from ALL_INDEXES WHERE OWNER in ('schema_name') ) i l LEFT OUTER JOIN ( WITH cons_ind AS -- Primary key and unique key constraints (SELECT constraint_name FROM ALL_CONSTRAINTS WHERE owner in ('schema_name') AND constraint_type IN ('P', 'U') AND constraint_name = index_name), cons_col AS --Column information of the constraints (SELECT ci.constraint_name, cc.position, cc.column_name FROM cons_ind ci, ALL_CONS_COLUMNS cc WHERE cc.owner in ('schema_name') AND cc.constraint_name = ci.constraint_name), ind_col AS -- Column information of the indexes (SELECT ic.index_name, ic.column_position, ic.column_name FROM cons_ind ci, ALL_IND_COLUMNS ic WHERE ic.index_owner in ('schema_name') AND ic.index_name = ci.constraint_name), not_match_ind AS -- The column information of the constraints does not match that of the indexes. (SELECT cc.constraint_name, ic.index_name FROM cons_col cc FULL OUTER JOIN ind_col ic ON cc.constraint_name = ic.index_name AND cc.position = ic.column_position AND cc.column_name = ic.column_name WHERE cc.constraint_name IS NULL OR ic.index_name IS NULL ) SELECT ci.constraint_name FROM cons_ind ci LEFT OUTER JOIN ( SELECT constraint_name FROM not_match_ind WHERE constraint_name IS NOT NULL UNION SELECT index_name FROM not_match_ind WHERE index_name IS NOT NULL ) nm ON ci.constraint_name = nm.constraint_name WHERE nm.constraint_name IS NULL ) fil_cons_not ON il.index_name = fil_cons_not.constraint_name WHERE fil_cons_not.CONSTRAINT_NAME is null;
SELECTcount(*) FROM ALL_CONSTRAINTS WHERE owner in ('schema_name') AND constraint_type IN ('P', 'U') AND constraint_name = index_name;
select owner,OBJECT_NAME, object_type as objectType, count(*) as count from dba_objects where object_type in ('INDEX') and owner in ('schema_name') and OBJECT_NAME not in( SELECT index_name FROM (select owner, index_name, status from ALL_INDEXES WHERE OWNER in ('schema_name') ) il LEFT OUTER JOIN ( WITH cons_ind AS -- Primary key and unique key constraints (SELECT constraint_name FROM ALL_CONSTRAINTS WHERE owner in ('schema_name') AND constraint_type IN ('P', 'U') AND constraint_name = index_name), cons_col AS --Column information of constraints (SELECT ci.constraint_name, cc.position, cc.column_name FROM cons_ind ci, ALL_CONS_COLUMNS cc WHERE cc.owner in ('schema_name') AND cc.constraint_name = ci.constraint_name), ind_col AS -- Column information of the indexes (SELECT ic.index_name, ic.column_position, ic.column_name FROM cons_ind ci, ALL_IND_COLUMNS ic WHERE ic.index_owner in ('schema_name') AND ic.index_name = ci.constraint_name), not_match_ind AS -- The column information of the constraints does not match that of the indexes. (SELECT cc.constraint_name, ic.index_name FROM cons_col cc FULL OUTER JOIN ind_col ic ON cc.constraint_name = ic.index_name AND cc.position = ic.column_position AND cc.column_name = ic.column_name WHERE cc.constraint_name IS NULL OR ic.index_name IS NULL ) SELECT ci.constraint_name FROM cons_ind ci LEFT OUTER JOIN ( SELECT constraint_name FROM not_match_ind WHERE constraint_name IS NOT NULL UNION SELECT index_name FROM not_match_ind WHERE index_name IS NOT NULL ) nm ON ci.constraint_name = nm.constraint_name WHERE nm.constraint_name IS NULL ) fil_cons_not ON il.index_name = fil_cons_not.constraint_name WHERE fil_cons_not.CONSTRAINT_NAME is null) and OBJECT_NAME not in (SELECT constraint_name FROM ALL_CONSTRAINTS WHERE owner in ('schema_name') AND constraint_type IN ('P', 'U') AND constraint_name = index_name) group by owner, object_type,OBJECT_NAME;
- The total number of indexes queried in the source Oracle database is equal to the sum of the preceding three parts.
- schema_name indicates the name of the schemas to be migrated. Replace it with the actual schema name.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot