SQL> 
SQL> /*
DOC>How do you support a unique constraint with a non-unique index?
DOC>
DOC>How do you avoid uk index maintainence during DML?
DOC>
DOC>This is identical to that of primary key
DOC>*/
SQL> 
SQL> drop table test3_a;
drop table test3_a
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> create table test3_a as select * from test3 where 1=2;

Table created.

SQL> insert into test3_a values('a',1);

1 row created.

SQL> insert into test3_a values('b',2);

1 row created.

SQL> -- Normal UK
SQL> alter table test3_a add constraint test3_a_UK UNIQUE(c1);

Table altered.

SQL> select STATUS,VALIDATED,INVALID from user_constraints where CONSTRAINT_NAME='TEST3_A_UK';

STATUS   VALIDATED     INVALID                                                  
-------- ------------- -------                                                  
ENABLED  VALIDATED                                                              

SQL> select uniqueness,status from user_indexes where index_name='TEST3_A_UK';

UNIQUENES STATUS                                                                
--------- --------                                                              
UNIQUE    VALID                                                                 

SQL> 
SQL> drop table test3_a;

Table dropped.

SQL> create table test3_a as select * from test3 where 1=2;

Table created.

SQL> insert into test3_a values('a',1);

1 row created.

SQL> insert into test3_a values('a',1);

1 row created.

SQL> create index test3_a_UK on test3_a(c1);

Index created.

SQL> -- A UK built on top of a non-unique index.
SQL> alter table test3_a add constraint test3_a_UK UNIQUE(c1) enable novalidate;

Table altered.

SQL> select STATUS,VALIDATED,INVALID from user_constraints where CONSTRAINT_NAME='TEST3_A_UK';

STATUS   VALIDATED     INVALID                                                  
-------- ------------- -------                                                  
ENABLED  NOT VALIDATED                                                          

SQL> select uniqueness,status from user_indexes where index_name='TEST3_A_UK';

UNIQUENES STATUS                                                                
--------- --------                                                              
NONUNIQUE VALID                                                                 

SQL> -- A UK that allows existing bad data, but does not allow more bad data.
SQL> insert into test3_a values('a',1);
insert into test3_a values('a',1)
*
ERROR at line 1:
ORA-00001: unique constraint (JYANG.TEST3_A_UK) violated 


SQL> 
SQL> -- Avoid non-unique UK index maintainenece during DML.
SQL> alter index test3_a_UK unusable;

Index altered.

SQL> insert into test3_a values('f',1);
insert into test3_a values('f',1)
*
ERROR at line 1:
ORA-01502: index 'JYANG.TEST3_A_UK' or partition of such index is in unusable 
state 


SQL> alter session set skip_unusable_indexes=true;

Session altered.

SQL> insert into test3_a values('f',1);
insert into test3_a values('f',1)
*
ERROR at line 1:
ORA-01502: index 'JYANG.TEST3_A_UK' or partition of such index is in unusable 
state 


SQL> -- Must also disable UK constraint
SQL> alter table test3_a modify constraint test3_a_UK disable keep index;

Table altered.

SQL> select STATUS,VALIDATED,INVALID from user_constraints where CONSTRAINT_NAME='TEST3_A_UK';

STATUS   VALIDATED     INVALID                                                  
-------- ------------- -------                                                  
DISABLED NOT VALIDATED                                                          

SQL> select uniqueness,status from user_indexes where index_name='TEST3_A_UK';

UNIQUENES STATUS                                                                
--------- --------                                                              
NONUNIQUE UNUSABLE                                                              

SQL> insert into test3_a values('f',1);

1 row created.

SQL> -- After DML, rebuild the index
SQL> alter index test3_a_UK rebuild;

Index altered.

SQL> -- Enable UK, as long as there is bad data, it can not be validated.
SQL> alter table test3_a modify constraint test3_a_UK enable novalidate;

Table altered.

SQL> select STATUS,VALIDATED,INVALID from user_constraints where CONSTRAINT_NAME='TEST3_A_UK';

STATUS   VALIDATED     INVALID                                                  
-------- ------------- -------                                                  
ENABLED  NOT VALIDATED                                                          

SQL> select uniqueness,status from user_indexes where index_name='TEST3_A_UK';

UNIQUENES STATUS                                                                
--------- --------                                                              
NONUNIQUE VALID                                                                 

SQL> select * from test3_a;

C1         C2                                                                   
-- ----------                                                                   
a           1                                                                   
a           1                                                                   
f           1                                                                   

SQL> -- What you have here is a functioning UK that has duplicates.
SQL> insert into test3_a values('f',1);
insert into test3_a values('f',1)
*
ERROR at line 1:
ORA-00001: unique constraint (JYANG.TEST3_A_UK) violated 


SQL> 
SQL> alter session set skip_unusable_indexes=false;

Session altered.

SQL> drop table test3_a;

Table dropped.

SQL> 
SQL> spool off