index and constraint
How can I check if I have the right indexes for the foreign key constraints on a child table ?
Since any index always add some space and resource overhead, it is probably unwise to blindly index every foreign key. But, if there are legitimate locking or query performance benefits to be made, the following script authored by Tom Kyte gives a list of foreign keys, their columns and a flag indicating whether the appropriate index exists.
column columns format a20 word_wrapped
column table_name format a30 word_wrapped
select decode( b.table_name, NULL, '****', 'ok' ) Status,
a.table_name, a.columns, b.columns
from
( select substr(a.table_name,1,30) table_name,
substr(a.constraint_name,1,30) constraint_name,
max(decode(position, 1, substr(column_name,1,30),NULL)) ||
max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||
max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
from user_ind_columns
group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
and b.columns (+) like a.columns || '%'
/
http://www.jlcomp.demon.co.uk/faq/fk_ind.html
How can I identify which index represents which primary or unique key constraint ?
The connection between constraints and the indexes which are used to check these constraints for the current user can be described by this query:
select --+ rule
o.owner as index_owner
, o.object_name as index_name
, n.name as constraint_name
from sys.cdef$ c
, dba_objects o
, sys.con$ n
where c.enabled = o.object_id
and c.con# = n.con#
and n.owner# = uid
/
SQL> select uid from dba_users where username like '%RXADMIN%';
If you leave away the condition and n.owner# = uid you get all the constraints. You may further limit this query to your constraint name by adding the condition and n.name = ‘your_constraint_name’.
Why can indexes and constraints be so different? In particular, you may use for example an index on columns (c, a, b) to enable a unique constraint on columns (a, b, c). Remember a constraint is a logical structure whereas an index is a physical one. So a unique or a primary constraint just describe the uniqueness. If (c, a, b) is unique then all other permutations are unique as well.
Further, these indexes may also be non-unique. You need this if you have a deferred constraint that is checked only at commit time. If you would insist on a unique index the attempt to insert duplicate values would fail before the commit although another command may have undone the duplicate entry.
http://www.jlcomp.demon.co.uk/faq/constraints_and_indexes.html
