Re: Unusable index in Partitioned table

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 27 May 2003 11:26:09 -0700
Message-ID: <130ba93a.0305271026.318664c3@posting.google.com>

depifster_at_yahoo.com (Jack Silvey) wrote in message news:<25c7944d.0305261550.4689f8da_at_posting.google.com>...
> Jusung,
>
> I believe you are right. Excellent demonstration.
>
> I wonder what would happen if you tried to enforce a unique / primary
> key constraint with a partitioned index, and marked a partition
> unusable?
>
> Jack
>
>

Yea, kinda wonder about it myself. This is one them I-will-sort-it-out-when-the-time-comes kind of questions. What about global indexes? What if the indexes are non-prefixed? What about equi-partitioing and non-equipartitioing factor? You would think they should not matter? But one can never be sure when it comes to partitioning...

In any case, unique constraints will not be invalidated when a index partition through which the constraint is enforced is disbaled.

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>

• Jusung Yang

Received on Tue May 27 2003 - 13:26:09 CDT