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') ) 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 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