Monday, November 15, 2010

ORA-01502, constraint, unique index and skip_unusable_indexes=true

This example shows the difference between unique indexes and non-unique indexes used to enforce primary key/unique constraints.

beside this, the only way to enable primary key/unique constraint to deferred is using non-unique index.


SQL> define _editor=vi
SQL> create table emp (
2 empno number(5),
3 ssn number(5),
4 constraint emp_pk primary key(empno) using index
5 (create unique index emp_pk on emp(empno) tablespace users),
6 constraint emp_unique_ssn unique(ssn) using index
7 (create unique index emp_u1 on emp(ssn) tablespace users)
8* )
9 /

Table created.

SQL> alter index emp_pk unusable;

Index altered.

SQL>
SQL>

SQL> insert into emp (empno) select employee_id from hr.employees;
insert into emp (empno) select employee_id from hr.employees
*
ERROR at line 1:
ORA-01502: index 'DONGHUA.EMP_PK' or partition of such index is in unusable
state


SQL> alter table emp disable constraint emp_pk keep index;

Table altered.

SQL> insert into emp (empno) select employee_id from hr.employees;
insert into emp (empno) select employee_id from hr.employees
*
ERROR at line 1:
ORA-01502: index 'DONGHUA.EMP_PK' or partition of such index is in unusable
state


SQL>
SQL> show parameter index

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_use_invisible_indexes boolean FALSE
skip_unusable_indexes boolean TRUE
SQL> drop index emp_pk;

Index dropped.

SQL> create index emp_pk on emp(empno) unusable;

Index created.

SQL> insert into emp (empno) select employee_id from hr.employees;

107 rows created.

SSQL> commit;

Commit complete.

SQL> alter table emp enable constraint emp_pk;
alter table emp enable constraint emp_pk
*
ERROR at line 1:
ORA-14063: Unusable index exists on unique/primary constraint key


SQL> alter index emp_pk rebuild;

Index altered.

SQL> alter table emp enable constraint emp_pk;

Table altered.

SQL> truncate table emp;

Table truncated.

SQL> alter index emp_pk unusable;

Index altered.

SQL> insert into emp (empno) select employee_id from hr.employees;
insert into emp (empno) select employee_id from hr.employees
*
ERROR at line 1:
ORA-01502: index 'DONGHUA.EMP_PK' or partition of such index is in unusable
state


SQL> alter table emp disable constraint emp_pk keep index;

Table altered.

SQL> insert into emp (empno) select employee_id from hr.employees;

107 rows created.

SQL>