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
;