RE: pros and cons (now: KEEP INDEX)

> -----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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: