*Unique constraints will not be invalidated when a index partition through which the 
constraint is enforced is disabled.


SQL> drop table part_range_temp;

Table dropped.

SQL> 
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 (300)
 13   (subpartition p3_d1 values('A'),
 14    subpartition p3_d2 values('B')
 15   )
 16  );

Table created.

SQL> 

-- Unique index
SQL> create unique index part_range_temp_idx on part_range_temp (c1, c3)
  2  local;

Index created.

SQL> alter table part_range_temp add constraint part_range_temp_cons_u 
  2        unique (c1, c3) enable validate;

Table altered.

-- Existing index used to enforced the unique constraint
SQL> select index_name from user_indexes where table_name='PART_RANGE_TEMP';

INDEX_NAME
------------------------------
PART_RANGE_TEMP_IDX

SQL> 
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> 
SQL> alter index part_range_temp_idx modify subpartition p1_d1 unusable;

Index altered.

-- Constraint remains valid
SQL> select status, validated, bad, invalid, last_change from user_constraints where CONSTRAINT_NAME

  2  =upper('part_range_temp_cons_u');

STATUS   VALIDATED     BAD INVALID LAST_CHANGE
-------- ------------- --- ------- ---------------
ENABLED  VALIDATED                 270503 11:17:44

SQL> 
SQL> insert into part_range_temp values(160, 'p2', 'A');

1 row created.

-- Data is still being validated
SQL> insert into part_range_temp values(160, 'p2', 'A');
insert into part_range_temp values(160, 'p2', 'A')
*
ERROR at line 1:
ORA-00001: unique constraint (JYANG.PART_RANGE_TEMP_CONS_U) violated


SQL> 
SQL> alter index PART_RANGE_TEMP_IDX unusable;

Index altered.

SQL> select PARTITION_NAME,SUBPARTITION_NAME,STATUS from user_ind_subpartitions 
  2  where index_name='PART_RANGE_TEMP_IDX';

PARTITION_NAME                 SUBPARTITION_NAME              STATUS
------------------------------ ------------------------------ --------
P1                             P1_D1                          UNUSABLE
P1                             P1_D2                          UNUSABLE
P2                             P2_D1                          UNUSABLE
P2                             P2_D2                          UNUSABLE
P3                             P3_D1                          UNUSABLE
P3                             P3_D2                          UNUSABLE

6 rows selected.

SQL> 

-- Nothing works, if the whole index is IU state.
SQL> insert into part_range_temp values(160, 'p2', 'A');
insert into part_range_temp values(160, 'p2', 'A')
*
ERROR at line 1:
ORA-01502: index 'JYANG.PART_RANGE_TEMP_IDX' or partition of such index is in
unusable state


SQL> 
SQL>