SQL> 
SQL> /*
DOC>How do you support a PK with a non-unique index?
DOC>
DOC>How do you avoid pk index maintainence during DML?
DOC>
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 PK
SQL> alter table test3_a add constraint test3_a_pk primary key (c1);

Table altered.

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

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

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

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_pk on test3_a(c1);

Index created.

SQL> -- A PK built on top of a non-unique index.
SQL> alter table test3_a add constraint test3_a_pk primary key (c1) enable novalidate;

Table altered.

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

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

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

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

SQL> -- A PK 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_PK) violated 


SQL> 
SQL> -- Avoid non-unique PK index maintainenece during DML.
SQL> alter index test3_a_pk 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_PK' 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_PK' or partition of such index is in unusable 
state 


SQL> -- Must also disable PK constraint
SQL> alter table test3_a modify constraint test3_a_pk disable keep index;

Table altered.

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

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

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

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_pk rebuild;

Index altered.

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

Table altered.

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

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

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

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 PK 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_PK) violated 


SQL> 
SQL> alter session set skip_unusable_indexes=false;

Session altered.

SQL> drop table test3_a;

Table dropped.

SQL> 
SQL> spool off