2009-06-02

Unique index candidates

Jonathan Lewis is blogging about indexes that could benefit from rebuild and Richard Foote is pointing out that preferably use unique indexes if possible. Putting these together. Indexes, that could be unique and are not, should be rebuild. Here is a way to find candidates.


SELECT DISTINCT table_name, index_name AS unique_index_candidate
, constraint_name AS based_on_constraint
, status AS constraint_status
FROM (SELECT con.table_name, con.constraint_name, con.m
, ind.index_name, con.status
, COUNT (con.column_name)
OVER (PARTITION BY con.table_name
, con.constraint_name
, ind.index_name) n
FROM (SELECT c.table_name, c.constraint_name
, o.column_name
, c.status
, MAX (o.POSITION)
OVER (PARTITION BY c.owner
, c.table_name
, c.constraint_name) m
FROM user_cons_columns o
INNER JOIN user_constraints c
ON o.constraint_name = c.constraint_name
WHERE c.constraint_type IN ('P', 'U')
AND c.DEFERRABLE = 'NOT DEFERRABLE') con
INNER JOIN
(SELECT i.table_name, i.index_name, n.column_name
FROM user_ind_columns n INNER JOIN user_indexes i
ON n.index_name = i.index_name
WHERE i.uniqueness = 'NONUNIQUE') ind
ON con.table_name = ind.table_name
AND con.column_name = ind.column_name
)
WHERE n = m
ORDER BY table_name, index_name
;

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.