Re: How to insert data using index unusable/rebuild

You intentionally bypassed the validation of the constraint and then make the claim that PK constraints allow duplicate values? If I intentionally fail to check the oil level in my car, I can't then claim that cars do not need any maintenance on the oil.

PK constraints do not allow duplicates. If you bothered to validate the constraint, you will find that an error is raised due to duplicate PK values. Not validating the constraint after you've created it is not the same as saying that the constraint does not hold. It never had a chance to!!

Cheers,
Brian

Jusung Yang wrote:
>
> 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

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"