RE: pros and cons (now: KEEP INDEX)

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 1 Apr 2004 17:33:33 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F602621033DAF8F@irvmbxw02>

> -----Original Message-----
> Connor McDonald

>=20

> Has anyone come up with a reason why you'd want the concept=20
> of a KEEP INDEX option?

I can always think of a hypothetical.
e.g.
I have a table with a PK. I want to load data in the table, and the data = may violate the PK (nulls in PK columns). I disable the constraint but = keep the index; I then use "enable constraint exceptions into ..." to = find the rows that violate the constraint and process those rows. = Finally I enable the constraint again and don't have to rebuild the = index.

proof of concept
SQL> create table t (id number, name varchar2 (10), 2 constraint t_pk primary key (id)) ; Table cr=E9=E9e.

SQL> insert into t (id, name) values (1, 'ONE') ; 1 ligne cr=E9=E9e.
SQL> insert into t (id, name) values (2, 'TWO') ; 1 ligne cr=E9=E9e.
SQL> commit ;
Validation effectu=E9e.

SQL> alter table t disable constraint t_pk keep index ; Table modifi=E9e.

SQL> insert into t (id, name) values (3, 'THREE') ; 1 ligne cr=E9=E9e.
SQL> ---- inserting row that violates constraint SQL> insert into t (id, name) values (null, 'FOUR') ; 1 ligne cr=E9=E9e.
SQL> commit ;
Validation effectu=E9e.

SQL> ----- find rows that violate constraint SQL> alter table t enable constraint t_pk exceptions into my_exceptions = ;
alter table t enable constraint t_pk exceptions into my_exceptions *
ERREUR =E0 la ligne 1 :
ORA-02437: cannot validate (JRK.T_PK) - primary key violated

SQL> ----- process rows that violate constraint SQL> delete from t where rowid in (select row_id from my_exceptions) ; 1 ligne supprim=E9e.
SQL> commit ;
Validation effectu=E9e.

SQL> alter table t enable constraint t_pk exceptions into my_exceptions = ;
Table modifi=E9e.=20

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Apr 01 2004 - 19:30:05 CST