# 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') ) 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;
```
2. 查询primary key+unique 约束总数：
```
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;
```
![](https://support.huaweicloud.com/ugo_faq/public_sys-resources/note_3.0-zh-cn.png)
- 源库Oracle查询的index总数等于以上三部分数量之和。
- 'schema_name' 为迁移项目要迁移的schema_name，请以实际情况替换。