查看有哪些索引含有重复的字段, 从而让索引更加合理化!
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_factor
FROM (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_mx
FROM ( SELECT table_owner,
table_name,
index_owner,
index_name,
SUBSTR (SYS_CONNECT_BY_PATH (column_name, ','),
2)
column_name_list_dup,
dup
FROM (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_name
ORDER BY column_position
)
AS seq,
COUNT(1)
OVER (
PARTITION BY table_owner,
table_name,
column_name,
column_position
)
AS dup
FROM sys.dba_ind_columns
WHERE ( index_owner LIKE 'E%'
OR index_owner LIKE 'TRIAL%'
OR index_owner = 'SCOTT')
AND index_owner NOT IN ('EXFSYS'))
WHERE dup != 1
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq
AND PRIOR index_owner = index_owner
AND 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_list
FROM (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_name
ORDER BY column_position)
AS seq
FROM sys.dba_ind_columns
WHERE ( index_owner LIKE 'E%'
OR index_owner LIKE 'TRIAL%'
OR index_owner = 'SCOTT')
AND index_owner NOT IN ('EXFSYS'))
WHERE seq = cnt
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq
AND PRIOR index_owner = index_owner
AND PRIOR index_name = index_name) b,
dba_indexes i
WHERE a.dup = a.dup_mx
AND a.index_owner = b.index_owner
AND a.index_name = b.index_name
AND a.index_owner = i.owner
AND a.index_name = i.index_name
ORDER BY a.table_owner, a.table_name, column_name_list_dup;
例如,在scott用户下面的emp表的empno上创建一个索引,然后再empno,deptno2列上创建复合索引,使用以上语句,查询的结果如下所示
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_factor
FROM (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_mx
FROM ( SELECT table_owner,
table_name,
index_owner,
index_name,
SUBSTR (SYS_CONNECT_BY_PATH (column_name, ','),
2)
column_name_list_dup,
dup
FROM (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_name
ORDER BY column_position
)
AS seq,
COUNT(1)
OVER (
PARTITION BY table_owner,
table_name,
column_name,
column_position
)
AS dup
FROM sys.dba_ind_columns
WHERE ( index_owner LIKE 'E%'
OR index_owner LIKE 'TRIAL%'
OR index_owner = 'SCOTT')
AND index_owner NOT IN ('EXFSYS'))
WHERE dup != 1
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq
AND PRIOR index_owner = index_owner
AND 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_list
FROM (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_name
ORDER BY column_position)
AS seq
FROM sys.dba_ind_columns
WHERE ( index_owner LIKE 'E%'
OR index_owner LIKE 'TRIAL%'
OR index_owner = 'SCOTT')
AND index_owner NOT IN ('EXFSYS'))
WHERE seq = cnt
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq
AND PRIOR index_owner = index_owner
AND PRIOR index_name = index_name) b,
dba_indexes i
WHERE a.dup = a.dup_mx
AND a.index_owner = b.index_owner
AND a.index_name = b.index_name
AND a.index_owner = i.owner
AND a.index_name = i.index_name
ORDER BY a.table_owner, a.table_name, column_name_list_dup;
例如,在scott用户下面的emp表的empno上创建一个索引,然后再empno,deptno2列上创建复合索引,使用以上语句,查询的结果如下所示
SCOTT
EMP01
SCOTT
I_EMPNO_EMP01
EMPNO
EMPNO
SCOTT
EMP01
SCOTT
I_EMPNO_DEPTNO
EMPNO,DEPTNO
EMPNO
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25794484/viewspace-732063/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25794484/viewspace-732063/