SQL> SQL> /* DOC>alter table modify constraint DOC>-------------------------------- DOC>enable validate: normal DOC> Alter index unusable: DOC> select * from table allowed - full scan DOC> any other queries with where clause will fail DOC> DML not allowed. DOC> set skip_unusable_indexes has no effect on unique indexes. DOC>enable novalidate: DOC> constraint is enforced DOC> index is maintained DOC> DML allowed DOC> This unique cons state has no use. It is has the same meaning as DOC> enable validate. Table contains non-unique data can not be created DOC> with a constraint in this state - unlike the FK constraint. DOC>disable validate: DOC> index gone DOC> no DML allowed, forget about constraint DOC>disable novalidate: DOC> constraint still there, but not enforced DOC> index gone DOC> DOC> DOC>alter table disable constraint keep index -> index good, constraint not active DOC>------------------------------------------------------------------------------- DOC> constraint will be in "disable, novalidate" state. DOC> unique index is still there. DOC> as long as there is an unique index, uniqueness will be enforced. DOC> DOC> DOC>alter index unusable -> constraint good, index unusable DOC>-------------------------------------------------------- DOC> No select against unique key, no DML allowed. DOC> DOC> DOC>Bottom line, there is no way to avoid unique index maintanence during DML. DOC> DOC>Note the differences between a stand-alone unique index and a unique constraint. 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> insert into test3_a values('c',3); 1 row created. SQL> insert into test3_a values('e',4); 1 row created. SQL> alter table test3_a add constraint test3_a_u unique (c2); Table altered. SQL> SQL> alter table test3_a modify constraint test3_a_u enable novalidate; Table altered. SQL> select STATUS,VALIDATED,INVALID from user_constraints where CONSTRAINT_NAME='TEST3_A_U'; STATUS VALIDATED INVALID -------- ------------- ------- ENABLED NOT VALIDATED SQL> insert into test3_a values('p',2); insert into test3_a values('p',2) * ERROR at line 1: ORA-00001: unique constraint (JYANG.TEST3_A_U) violated SQL> SQL> alter table test3_a modify constraint test3_a_u disable validate; Table altered. SQL> insert into test3_a values('p',2); insert into test3_a values('p',2) * ERROR at line 1: ORA-25128: No insert/update/delete on table with constraint (JYANG.TEST3_A_U) disabled and validated 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('b',2); 1 row created. SQL> insert into test3_a values('c',1); 1 row created. SQL> -- Important: ORACLE tried to create an unique index on c2 and failed. SQL> -- Had a non-unique index existed, the statement would have succeeded. SQL> alter table test3_a add constraint test3_a_u unique (c2) enable novalidate; alter table test3_a add constraint test3_a_u unique (c2) enable novalidate * ERROR at line 1: ORA-02299: cannot validate (JYANG.TEST3_A_U) - duplicate keys found SQL> SQL> delete from test3_a where c1='c'; 1 row deleted. SQL> alter table test3_a add constraint test3_a_u unique (c2); Table altered. SQL> SQL> -- What happens when unique index is unusable? SQL> alter index test3_a_u unusable; Index altered. SQL> select STATUS,VALIDATED,INVALID from user_constraints where CONSTRAINT_NAME='TEST3_A_U'; STATUS VALIDATED INVALID -------- ------------- ------- ENABLED VALIDATED SQL> select * from test3_a; C1 C2 -- ---------- a 1 b 2 SQL> select * from test3_a where c2=1; select * from test3_a where c2=1 * ERROR at line 1: ORA-01502: index 'JYANG.TEST3_A_U' or partition of such index is in unusable state SQL> insert into test3_a values('c',3); insert into test3_a values('c',3) * ERROR at line 1: ORA-01502: index 'JYANG.TEST3_A_U' or partition of such index is in unusable state SQL> SQL> -- skip_unusable_indexes useless against unique indexes SQL> alter session set skip_unusable_indexes=true; Session altered. SQL> select * from test3_a where c2=1; select * from test3_a where c2=1 * ERROR at line 1: ORA-01502: index 'JYANG.TEST3_A_U' or partition of such index is in unusable state SQL> insert into test3_a values('c',3); insert into test3_a values('c',3) * ERROR at line 1: ORA-01502: index 'JYANG.TEST3_A_U' or partition of such index is in unusable state SQL> SQL> alter session set skip_unusable_indexes=false; Session altered. SQL> alter index test3_a_u rebuild; Index altered. SQL> -- What happens when an unique constraint is disabled and index kept? SQL> alter table test3_a disable constraint test3_a_u keep index; Table altered. SQL> select STATUS,VALIDATED,INVALID from user_constraints where CONSTRAINT_NAME='TEST3_A_U'; STATUS VALIDATED INVALID -------- ------------- ------- DISABLED NOT VALIDATED SQL> select uniqueness, status from user_indexes where index_name='TEST3_A_U'; UNIQUENES STATUS --------- -------- UNIQUE VALID SQL> -- unique index is still present and valid, so select will work and uniqueness will be enforeced. SQL> select * from test3_a where c2=1; C1 C2 -- ---------- a 1 SQL> insert into test3_a values('c',2); insert into test3_a values('c',2) * ERROR at line 1: ORA-00001: unique constraint (JYANG.TEST3_A_U) violated SQL> insert into test3_a values('c',3); 1 row created. SQL> SQL> -- Constraint good, index unusable -> no select against index key, no DML allowed. SQL> alter table test3_a enable constraint test3_a_u; Table altered. SQL> alter index test3_a_u unusable; Index altered. SQL> select STATUS,VALIDATED,INVALID from user_constraints where CONSTRAINT_NAME='TEST3_A_U'; STATUS VALIDATED INVALID -------- ------------- ------- ENABLED VALIDATED SQL> select uniqueness, status from user_indexes where index_name='TEST3_A_U'; UNIQUENES STATUS --------- -------- UNIQUE UNUSABLE SQL> select * from test3_a where c2=1; select * from test3_a where c2=1 * ERROR at line 1: ORA-01502: index 'JYANG.TEST3_A_U' or partition of such index is in unusable state SQL> insert into test3_a values('c',2); insert into test3_a values('c',2) * ERROR at line 1: ORA-01502: index 'JYANG.TEST3_A_U' or partition of such index is in unusable state SQL> insert into test3_a values('c',3); insert into test3_a values('c',3) * ERROR at line 1: ORA-01502: index 'JYANG.TEST3_A_U' or partition of such index is in unusable state SQL> SQL> SQL> alter session set skip_unusable_indexes=false; Session altered. SQL> drop table test3_a; Table dropped. SQL> SQL> spool off