All the good stuff about setting an index partition "unusable" during data loading. Everything works if you just set skip_unusbale_indexes=true and leave the unusable index partitions alone. !!! Strangely skip_unusbale_indexes fails with non-partitioned indexes. Works as expected with partitioned local prefixed indexes - tested on range-list partitioned indexes. Insert, delete fail on the unusable partitions as expected. -- Tested with Oracle9i Release 9.2.0.1.0 on WindowsXP SQL> create table part_range_temp (c1 number(3), c2 varchar2(2)) 2 partition by range (c1) 3 (partition p1 values less than (100), 4 partition p2 values less than (200), 5 partition p3 values less than (maxvalue)); Table created. SQL> SQL> create index part_range_temp_idx on part_range_temp (c1) 2 local; Index created. SQL> SQL> insert into part_range_temp values(50, 'p1'); 1 row created. SQL> insert into part_range_temp values(150, 'p2'); 1 row created. SQL> insert into part_range_temp values(250, 'p3'); 1 row created. SQL> alter index part_range_temp_idx modify partition p2 unusable; Index altered. SQL> SQL> set autotrace on explain -- Index not used, no problem SQL> select * from part_range_temp; C1 C2 ---------- -- 50 p1 150 p2 250 p3 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1227 Bytes=19 632) 1 0 PARTITION RANGE (ALL) 2 1 TABLE ACCESS (FULL) OF 'PART_RANGE_TEMP' (Cost=3 Card=12 27 Bytes=19632) -- Usable partition of the index accessed, no problem SQL> select * from part_range_temp where c1=50; C1 C2 ---------- -- 50 p1 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=64) 1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'PART_RANGE_TEMP' ( Cost=1 Card=4 Bytes=64) 2 1 INDEX (RANGE SCAN) OF 'PART_RANGE_TEMP_IDX' (NON-UNIQUE) (Cost=1 Card=2) -- Unusable partition of the index accessed, trouble SQL> select * from part_range_temp where c1=150; select * from part_range_temp where c1=150 * ERROR at line 1: ORA-01502: index 'JYANG.PART_RANGE_TEMP_IDX' or partition of such index is in unusable state SQL> alter session set skip_unusable_indexes=true; Session altered. -- Optimizer avoided index scan, OK now SQL> select * from part_range_temp where c1=150; C1 C2 ---------- -- 150 p2 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=64) 1 0 TABLE ACCESS (FULL) OF 'PART_RANGE_TEMP' (Cost=2 Card=4 By tes=64) -- The usable partitions of the index remain available to the optimizer. SQL> select * from part_range_temp where c1=50; C1 C2 ---------- -- 50 p1 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=64) 1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'PART_RANGE_TEMP' ( Cost=1 Card=4 Bytes=64) 2 1 INDEX (RANGE SCAN) OF 'PART_RANGE_TEMP_IDX' (NON-UNIQUE) (Cost=1 Card=2) SQL> -- Similarly for rang-list partitioning SQL> create table part_range_temp (c1 number(3), c2 varchar2(2), c3 char(1)) 2 partition by range (c1) 3 subpartition by list (c3) 4 (partition p1 values less than (100) 5 (subpartition p1_d1 values('A'), 6 subpartition p1_d2 values('B') 7 ), 8 partition p2 values less than (200) 9 (subpartition p2_d1 values('A'), 10 subpartition p2_d2 values('B') 11 ), 12 partition p3 values less than (maxvalue) 13 (subpartition p3_d1 values('A'), 14 subpartition p3_d2 values('B') 15 ) 16 ); Table created. SQL> create index part_range_temp_idx on part_range_temp (c1, c3) 2 local; Index created. SQL> select PARTITION_NAME, SUBPARTITION_NAME, SUBPARTITION_POSITION, status from 2 user_ind_subpartitions where index_name='PART_RANGE_TEMP_IDX'; PARTITION_NAME SUBPARTITION_NAME ------------------------------ ------------------------------ SUBPARTITION_POSITION STATUS --------------------- -------- P1 P1_D1 1 USABLE P1 P1_D2 2 USABLE P2 P2_D1 1 USABLE PARTITION_NAME SUBPARTITION_NAME ------------------------------ ------------------------------ SUBPARTITION_POSITION STATUS --------------------- -------- P2 P2_D2 2 USABLE P3 P3_D1 1 USABLE P3 P3_D2 2 USABLE 6 rows selected. SQL> insert into part_range_temp values(50, 'p1', 'A'); 1 row created. SQL> insert into part_range_temp values(50, 'p1', 'B'); 1 row created. SQL> insert into part_range_temp values(150, 'p2', 'A'); 1 row created. SQL> insert into part_range_temp values(150, 'p2', 'B'); 1 row created. SQL> insert into part_range_temp values(250, 'p3', 'A'); 1 row created. SQL> insert into part_range_temp values(250, 'p3', 'B'); 1 row created. SQL> alter index part_range_temp_idx modify subpartition p1_d1 unusable; Index altered. SQL> select PARTITION_NAME, SUBPARTITION_NAME, SUBPARTITION_POSITION, status from 2 user_ind_subpartitions where index_name='PART_RANGE_TEMP_IDX'; PARTITION_NAME SUBPARTITION_NAME ------------------------------ ------------------------------ SUBPARTITION_POSITION STATUS --------------------- -------- P1 P1_D1 1 UNUSABLE P1 P1_D2 2 USABLE P2 P2_D1 1 USABLE PARTITION_NAME SUBPARTITION_NAME ------------------------------ ------------------------------ SUBPARTITION_POSITION STATUS --------------------- -------- P2 P2_D2 2 USABLE P3 P3_D1 1 USABLE P3 P3_D2 2 USABLE 6 rows selected. SQL> select * from part_range_temp where c1=50 and c3='B'; C1 C2 C ---------- -- - 50 p1 B SQL> select * from part_range_temp where c1=50 and c3='A'; C1 C2 C ---------- -- - 50 p1 A SQL> alter session set skip_unusable_indexes=false; Session altered. SQL> select * from part_range_temp where c1=50 and c3='A'; select * from part_range_temp where c1=50 and c3='A' * ERROR at line 1: ORA-01502: index 'JYANG.PART_RANGE_TEMP_IDX' or partition of such index is in unusable state SQL> alter session set skip_unusable_indexes=true; Session altered. SQL> select * from part_range_temp where c1=50 and c3='A'; C1 C2 C ---------- -- - 50 p1 A SQL>