查看有哪些索引含有重复的字段, 从而让索引更加合理化!SQL> SELECT /*+ rule */ a .table_owner,a.table_name,a.index_owner,a.index_name,column_name_list,column_name_list_dup,dup duplicate_indexes,i.uniqueness,i.partitioned,i.leaf_blocks,i.distinct_keys,i.num_rows,i.clustering_factorFROM (SELECT table_owner,table_name,index_owner,index_name,column_name_list_dup,dup,MAX (dup)OVER (PARTITION BY table_owner, table_name, index_name)dup_mxFROM ( SELECT table_owner,table_name,index_owner,index_name,SUBSTR (SYS_CONNECT_BY_PATH (column_name, ','),2)column_name_list_dup,dupFROM (SELECT index_owner,index_name,table_owner,table_name,column_name,COUNT(1)OVER (PARTITION BY index_owner,index_name)cnt,ROW_NUMBER ()OVER (PARTITION BY index_owner,index_nameORDER BY column_position)AS seq,COUNT(1)OVER (PARTITION BY table_owner,table_name,column_name,column_position)AS dupFROM sys.dba_ind_columnsWHERE ( index_owner LIKE 'E%'OR index_owner LIKE 'TRIAL%'OR index_owner = 'SCOTT')AND index_owner NOT IN ('EXFSYS'))WHERE dup != 1START WITH seq = 1CONNECT BY PRIOR seq + 1 = seqAND PRIOR index_owner = index_ownerAND PRIOR index_name = index_name)) a,( SELECT table_owner,table_name,index_owner,index_name,SUBSTR (SYS_CONNECT_BY_PATH (column_name, ','), 2)column_name_listFROM (SELECT index_owner,index_name,table_owner,table_name,column_name,COUNT (1)OVER (PARTITION BY index_owner, index_name)cnt,ROW_NUMBER ()OVER (PARTITION BY index_owner, index_nameORDER BY column_position)AS seqFROM sys.dba_ind_columnsWHERE ( index_owner LIKE 'E%'OR index_owner LIKE 'TRIAL%'OR index_owner = 'SCOTT')AND index_owner NOT IN ('EXFSYS'))WHERE seq = cntSTART WITH seq = 1CONNECT BY PRIOR seq + 1 = seqAND PRIOR index_owner = index_ownerAND PRIOR index_name = index_name) b,dba_indexes iWHERE a.dup = a.dup_mxAND a.index_owner = b.index_ownerAND a.index_name = b.index_nameAND a.index_owner = i.ownerAND a.index_name = i.index_nameORDER BY a.table_owner, a.table_name, column_name_list_dup;
例如，在scott用户下面的emp表的empno上创建一个索引，然后再empno,deptno2列上创建复合索引，使用以上语句，查询的结果如下所示
SCOTTEMP01SCOTTI_EMPNO_EMP01EMPNO EMPNO
SCOTTEMP01SCOTTI_EMPNO_DEPTNOEMPNO,DEPTNOEMPNO
如果觉得《oracle 字段 查找重复 oracle数据库查询重复的索引列》对你有帮助，请点赞、收藏，并留下你的观点哦！
