Help Center/ Database and Application Migration UGO/ FAQs/ Migration Project/ Why Is the Number of Indexes Different After Oracle Data is Migrated to GaussDB?
Updated on 2024-11-07 GMT+08:00

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:

1. Query the number of indexes displayed on the UGO evaluation page.
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;
2. Query the total number of primary key and unique constraints.
SELECTcount(*) 
         FROM ALL_CONSTRAINTS 
         WHERE owner  in  ('schema_name')   AND constraint_type IN ('P', 'U')  AND constraint_name  = index_name;
3. Query the number of objects generated by the system.
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.