数据库和应用迁移 UGO-Oracle迁移至GaussDB完成后index总数查询:解决办法

时间：2024-10-18 19:42:31

解决办法

连接上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;

上一篇：数据库和应用迁移 UGO-ctid,xc_node_id,tableoid在GaussDB中已存在。:解决办法

下一篇：数据库和应用迁移 UGO-GaussDB（MySQL兼容模式）数据库GUC参数