It is very common for a data model to define a set of columns that must be unique in the table. This is enforced with a primary key, a unique key, or a unique index. These are all slightly different, but obviously related concepts. Any constraint that declares uniqueness (either a PK or UK) needs a unique index to help enforce that. A unique index maintains this by inserting a row in it’s b-tree for each row in the table that has at least one column that is not null. It is a fast operation for an insert or update to check for uniqueness if this index is in place, in fact it’s is simply the result of inserting the new leaf in the b-tree, if it succeeds then the uniqueness constraint has been satisfied.
This implies that the columns in the constraint and the index are an exact match.But what if that is not true? You might think that is an impossible case, but it can happen, and it’s worth monitoring for because of the potentially horrible effects of that index being missing (worst case – a full table scan to verify the uniqueness before insert or update of each record).
Fortunately, the check for this is very simple:
SELECT cc.owner, cc.table_name, c.index_name, cc.column_name, cc.position
FROM dba_cons_columns cc
JOIN dba_constraints c ON (c.owner = cc.owner AND c.constraint_name = cc.constraint_name)
WHERE cc.owner NOT LIKE ‘%SYS%’ AND c.constraint_type IN (‘P’, ‘U’)
MINUS
SELECT table_owner, table_name, index_name, column_name, column_position
FROM sys.dba_ind_columns;
This query finds all the columns that are involved in a unique constraint (a primary key also implies uniqueness, hence the P in the constraint_type filter), then it removes from that set all the columns that are indexed. The results are the index columns that do not match the constraints in column number and order. It may not be necessary to include the position of the columns in this check, but I like to find any discrepancies in order also because it implies a logical mismatch in the design.