c.column_position(+)
15 order by tab_name, ind_column;
OWNER CONS_NAME TAB_NAME CONS_COLUMN IND_COLUMN INDEX_NAME
------------------------- ------------------------------ -------------------- ------------------------- ------------------------- ------------------------------
AMY EMP_FK EMP DEPTNO ***Check index****
SQL> SQL>
方法一只是判断用户，对于单列约束。如下判断多列约束，另外还可以检测外键上的位图索引的存在，位图索引不能防止锁的发生；
####################3
col cols for a30;
col index_type for a10;
SET VERIFY OFF;
SET LINESIZE 200;
COL IND_COLUMN FOR A25;
COL CONS_COLUMN FOR A25;
COL index_NAME FOR A20;
COL OWNER FOR A25;
select case
when ind.index_name is not null then
case
when ind.index_type in ('BITMAP') then
'**Bitmp idx**'
else
'indexed'
end
else
'**Check idx**'
end checker,
ind.index_type,
cons.owner,
cons.table_name,
ind.index_name,
cons.constraint_name,
cons.cols
from (select c.owner,
c.table_name,
c.constraint_name,
listagg(cc.column_name, ',') within group(order by cc.column_name) cols
from dba_constraints c, dba_cons_columns cc
where c.owner = cc.owner
and c.owner = upper('&&schema')
and c.constraint_name = cc.constraint_name
and c.constraint_type = 'R'
group by c.owner, c.table_name, c.constraint_name) cons
left outer join (select table_owner,
table_name,
index_name,
index_type,
cbr,
listagg(column_name, ',') within group(order by column_name) cols
from (select ic.table_owner,
ic.table_name,
ic.index_name,
ic.column_name,
ic.column_position,
i.index_type,
connect_by_root(ic.column_name) cbr
from dba_ind_columns ic, dba_indexes i
where ic.table_owner = upper('&&schema')
and ic.table_owner = i.table_owner
and ic.table_name = i.table_name
and ic.index_name = i.index_name
connect by prior ic.column_position - 1 =
ic.column_position
and prior ic.index_name = ic.index_name)
group by table_owner,
table_name,
index_name,
index_type,
cbr) ind
on cons.cols = ind.cols
and cons.table_name = ind.table_name
and cons.owner = ind.table_owner
order by checker, cons.owner, cons.table_name;
#############################
eg：
SQL> col cols for a30;
SQL> col index_type for a10;
SQL> SET VERIFY OFF;
SQL> SET LINESIZE 200;
COL IND_COLUMN FOR A25;
SQL> SQL> COL CONS_COLUMN FOR A25;
SQL> COL index_NAME FOR A20;
SQL> COL OWNER FOR A25;
SQL> select case
2 when ind.index_name is not null then
3 case
4 when ind.index_type in ('BITMAP') then
5 '**Bitmp idx**'
6 else
'indexed'
7 8 end
9 else
10 '**Check idx**'
end checker,
11 12 ind.index_type,
13 cons.owner,
14 cons.table_name,
15 ind.index_name,
16 cons.constraint_name,
17 cons.cols
18 from (select c.owner,
19 c.table_name,
20 c.constraint_name,
21 listagg(cc.column_name, ',') within group(order by cc.column_name) cols
22 from dba_constraints c, dba_cons_columns cc
where c.owner = cc.owner
23 24 and c.owner = upper('&&schema')
25 and c.constraint_name = cc.constraint_name
26 and c.constraint_type = 'R'
27 group by c.owner, c.table_name, c.constraint_name) cons
28 left outer join (select table_owner,
29 table_name,
30 index_name,
31 index_type,
32 cbr,
33 listagg(column_name, ',') within group(order by column_name) cols
34 from (select ic.table_owner,
35 ic.table_name,
36 ic.index_na