文档首页/ 数据库和应用迁移 UGO/ 常见问题/ 迁移项目/ Oracle迁移至GaussDB完成后index总数查询
更新时间:2024-09-12 GMT+08:00
分享

Oracle迁移至GaussDB完成后index总数查询

问题现象

Oracle为源库迁移至GaussDB后,目标库GaussDB index总数与源库Oracle查询数量不一致。

问题原因

UGO 采集时将主键和唯一约束以及系统生成的索引过滤掉 ,导致迁移后源库和目标库index数量不同。

解决办法

连接上Oracle数据库执行以下SQL语句查询index数量。

1. ugo评估页面显示的index个数查询语句:
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   -- 主键和唯一键约束
        (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  -- 约束的列信息
        (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   -- 索引的列信息
        (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  -- 约束的列信息和索引的列信息不匹配
        (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. 查询primary key+unqiue 约束总数:
SELECTcount(*) 
         FROM ALL_CONSTRAINTS 
         WHERE owner  in  ('schema_name')   AND constraint_type IN ('P', 'U')  AND constraint_name  = index_name;
3. 系统生成的对象个数查询:
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   -- 主键和唯一键约束
        (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  -- 约束的列信息
        (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   -- 索引的列信息
        (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  -- 约束的列信息和索引的列信息不匹配
        (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;
  • 源库Oracle查询的index总数等于以上三部分数量之和。
  • 'schema_name' 为迁移项目要迁移的schema_name,请以实际情况替换。

相关文档