Database Reference
In-Depth Information
7 from ( select b.table_name,
8 b.constraint_name,
9 max(decode( position, 1, column_name, null )) cname1,
10 max(decode( position, 2, column_name, null )) cname2,
11 max(decode( position, 3, column_name, null )) cname3,
12 max(decode( position, 4, column_name, null )) cname4,
13 max(decode( position, 5, column_name, null )) cname5,
14 max(decode( position, 6, column_name, null )) cname6,
15 max(decode( position, 7, column_name, null )) cname7,
16 max(decode( position, 8, column_name, null )) cname8,
17 count(*) col_cnt
18 from (select substr(table_name,1,30) table_name,
19 substr(constraint_name,1,30) constraint_name,
20 substr(column_name,1,30) column_name,
21 position
22 from user_cons_columns ) a,
23 user_constraints b
24 where a.constraint_name = b.constraint_name
25 and b.constraint_type = 'R'
26 group by b.table_name, b.constraint_name
27 ) cons
28 where col_cnt > ALL
29 ( select count(*)
30 from user_ind_columns i,
31 user_indexes ui
32 where i.table_name = cons.table_name
33 and i.column_name in (cname1, cname2, cname3, cname4,
34 cname5, cname6, cname7, cname8 )
35 and i.column_position <= cons.col_cnt
36 and ui.table_name = i.table_name
37 and ui.index_name = i.index_name
38 and ui.index_type IN ('NORMAL','NORMAL/REV')
39 group by i.index_name
40 )
41 /
TABLE_NAME CONSTRAINT_NAME COLUMNS
--------------- --------------- ------------------------------
C SYS_C0061427 X
This script works on foreign key constraints that have up to eight columns in them (if you have more than that,
you probably want to rethink your design). It starts by building an inline view named
CONS
in the previous query. This
inline view transposes the appropriate column names in the constraint from rows into columns, with the result being
a row per constraint and up to eight columns that have the names of the columns in the constraint. Additionally,
there is a column,
COL_CNT
, which contains the number of columns in the foreign key constraint itself. For each row
returned from the inline view, we execute a correlated subquery that checks all of the indexes on the table currently
being processed. It counts the columns in that index that match columns in the foreign key constraint and then
groups them by index name. So, it generates a set of numbers, each of which is a count of matching columns in
some index on that table. If the original
COL_CNT
is greater than all of these numbers, then there is no index on that
table that supports that constraint. If
COL_CNT
is less than all of these numbers, then there is at least one index that
supports that constraint. Note the use of the
NVL2
function, which we used to “glue” the list of column names into a
