KEEP INDEX clause


In http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4941517587762#7082216367226 I proposed a use for the KEEP INDEX clause:

"Scenario:
- Partitioned table
- Unique or PK constraint backed by local (or otherwise partitioned) unique index
- SQLLDR Direct load, some rows violate constraint, so 1 or more index partitions unusable.

At this point you might want to:

- disable or drop constraint KEEP INDEX
- rebuild just those index partitions that are unusable
- enable or add constraint, exceptions into etc

The KEEP INDEX becomes useful because the constraint applies to the whole table, whereas we only want to rebuild those index *partitions* that are unusable. Without KEEP INDEX the entire index would need be to rebuilt/recreated."

but I also stresed:

"(This hypothesis wholly untested btw)"

Well a while back I did test it...

SQL> create table T
  2   ( p number,
  3     q number,
  4     r number )
  5  partition by range ( p )
  6   ( partition p1 values less than ( 100 ),
  7     partition p2 values less than ( 200 ) ,
  8     partition p3 values less than ( 300 )) ;

Table created.

SQL>
SQL> create unique index TX on T ( p ) local;

Index created.

SQL>
SQL> alter table T add constraint TX primary key ( p ) ;

Table altered.

SQL>
SQL> alter session set skip_unusable_indexes = true;

Session altered.

SQL>
SQL> alter table T disable constraint TX keep index;

Table altered.

SQL>
SQL> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
TX

SQL>
SQL> alter index TX modify partition p1 unusable;

Index altered.

SQL>
SQL> insert /*+ APPEND */ into T
  2  select rownum, rownum, rownum from all_Objects
  3  where rownum < 100;
insert /*+ APPEND */ into T
                          *
ERROR at line 1:
ORA-26027: unique index MCDONAC.TX partition P1 initially in unusable state

So insert-append does not work...So how about sqlldr

I created a control file of:

LOAD DATA
INFILE *
INTO TABLE T
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(p,q,r)
BEGINDATA
1,1,1
1,1,1
2,2,2
3,3,3
4,4,4
5,5,5
6,6,6
7,7,7
8,8,8
9,9,9
10,10,10

and ran

C:\oracle\ora92\bin>sqlldr userid=mcdonac/**** control=c:\c.ctl direct=true skip_index_maintenance=true

SQL*Loader: Release 9.2.0.5.0 - Production on Fri Jan 28 19:03:46 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Load completed - logical record count 11.

So maybe this is the reason its there ??? Well, lets repeat without using keep index....

SQL> drop table T;

Table dropped.

SQL>
SQL> create table T
  2   ( p number,
  3     q number,
  4     r number )
  5  partition by range ( p )
  6   ( partition p1 values less than ( 100 ),
  7     partition p2 values less than ( 200 ) ,
  8     partition p3 values less than ( 300 )) ;

Table created.

SQL>
SQL> create unique index TX on T ( p ) local;

Index created.

SQL>
SQL> alter table T add constraint TX primary key ( p ) ;

Table altered.

and then

C:\oracle\ora92\bin>sqlldr userid=mcdonac/**** control=c:\c.ctl direct=true skip_index_maintenance=true

SQL*Loader: Release 9.2.0.5.0 - Production on Fri Jan 28 19:09:31 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Load completed - logical record count 11.

Well there you go... we still loaded in there with no problems...The constraint was not used (since we've loaded duplicates). When we look at the database we see:
SQL> select partition_name, status
  2  from user_ind_partitions
  3  where index_name = 'TX';

PARTITION_NAME                 STATUS
------------------------------ --------
P1                             UNUSABLE
P2                             USABLE
P3                             USABLE

so we achieved the load without disabling the constraint at all...Even more interesting is:
SQL> select status, validated from user_constraints
  2  where constraint_name = 'TX';

STATUS   VALIDATED
-------- -------------
ENABLED  VALIDATED

so according to Oracle, our constraint is just fine, even though its backed by an unusable index partition. So we can do very cool things like:
SQL> insert into T values (200,200,200);

1 row created.

SQL> insert into T values (200,200,200);
insert into T values (200,200,200)
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.TX) violated

ie, successful constraint usage as long as we stay away from the index partition. So be careful with KEEP INDEX - it might not be the solution to all your problems...