Locking and Issues - Oracle Database Transactions and Locking Revealed - page 24

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 comma-separated list.
This function takes three arguments: A , B , C . If argument A is not null, then it returns argument B ; otherwise, it returns
argument C . This query assumes that the owner of the constraint is the owner of the table and index as well. If another
user indexed the table or the table is in another schema (both rare events), it will not work correctly.