Re: How to insert data using index unusable/rebuild

danielroy10junk_at_hotmail.com (Daniel Roy) wrote in message news:<3722db.0307111626.33f64346_at_posting.google.com>...
> JusungYang_at_yahoo.com (Jusung Yang) wrote in message
> > You are aware of the fact that SKIP_UNUSABLE_INDEXES does not work for
> > unique index, right? If your goal is to avoid hard coding the index
> > names in your process script, there is really no need to use
> > SKIP_UNUSABLE_INDEXES. If the goal is to avoid index maintainence
> > during DML, you are out of luck with UNIQUE indexes. You can use
> > SKIP_UNUSABLE_INDEXES to work with the primary key - since primary
> > keys do not have to be unique. But not unique indexes.
> >
> > Your table is not partitioned, yes? You might have better luck with
> > partitioned tables. They have more options for you to play around
> > with.
> >
> > - Jusung Yang
>
> Pardon me? Better review database constraints!: "... since primary
> keys do not have to be unique."! Here's a link:
> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/clauses3a.htm
>
> Daniel

There are often differences between what the theories say and how it is done in practice. Beware of the context. Saying "priamry keys need not be unique" is not the same as saying "primary key are not unique". It has been possible to support a PK constraint with non-unique index since ORACLE 8. This fact alone should raise the prospect that duplciate values may exist in the PK column. Here is an example of a PK containing duplicates and how you can avoid PK index maintainence during DML. Creating a non-unique index is the key.

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>

• Jusung Yang