2011-12-20

The index could be unique

drop table uq;

create table uq(pk int constraint uq_pk primary key, col int, col2 int);

create index uq_col_idx on uq(col,pk);

Index uq_col_idx is not unique but it might be defined as such. It contains all columns in a unique primary key. To find such candidates is just another relational division question.
with co as (
select con.constraint_name
     , con.table_name
     , cco.column_name
     , count(*) over (partition by con.constraint_name) ccc 
  from user_constraints con 
 inner join user_cons_columns cco 
    on cco.constraint_name=con.constraint_name 
 where con.constraint_type in ('P','U') 
   and con.deferrable = 'NOT DEFERRABLE' 
), ix as (
select idx.table_name,idx.index_name,ico.column_name,idx.uniqueness
  from user_indexes idx 
 inner join user_ind_columns ico 
    on idx.index_name = ico.index_name
 where exists (
   select 0 from user_tab_columns col
    inner join user_ind_columns coi 
    on coi.table_name = col.table_name
   and coi.column_name = col.column_name
    where idx.index_name = coi.index_name
      and col.nullable = 'N'
 )
), ixco as (
select ix.table_name
     , ix.index_name
     , ix.column_name
     , co.constraint_name
     , co.ccc
     , count(*) over (partition by co.constraint_name,ix.index_name) ixc
     , ix.uniqueness
  from co, ix
 where co.table_name=ix.table_name 
  and co.column_name=ix.column_name
)
select distinct table_name,index_name could_be_unique,constraint_name based_on
  from ixco
 where uniqueness = 'NONUNIQUE'
   and ccc=ixc
 order by table_name,index_name
;

TABLE_NAME                     COULD_BE_UNIQUE                BASED_ON
------------------------------ ------------------------------ ------------------------------
UQ                             UQ_COL_IDX                     UQ_PK
drop index uq_col_idx;

create unique index uq_col_idx on uq(col,pk);


No comments:

Post a Comment

About Me

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