Re: Unusable index in Partitioned table

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

JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0305252121.2fbdf215_at_posting.google.com>...
> depifster_at_yahoo.com (Jack Silvey) wrote in message news:<25c7944d.0305251055.2bb3c31a_at_posting.google.com>...
> > skip_unusable_indexes does not apply if your indexes are unique or
> > used to enforce unique or primary key constraint.
>
> This is true for non-partitioned indexes. For partitioned indexes
> skip_unusable_indexes works as expected. It is strange why ORACLE
> would not allow
> skip_unusable_indexes for non-partitioned indexes. You can not delete
> or insert into the unusable partition, that makes sense. For select
> you always have table scan to rely on.
>
> SQL> create table tyu as select object_id, object_name from
> user_objects;
>
> Table created.
>
> -- Non-partitioned unique index
> SQL> create unique index tyu_idx on tyu(object_id);
>
> Index created.
>
> SQL> select * from tyu where rownum <2;
>
> OBJECT_ID
> ----------
> OBJECT_NAME
> --------------------------------------------------------------------------------
> 46915
> A
>
>
> SQL> alter index tyu_idx unusable;
>
> Index altered.
>
> SQL> select * from tyu where object_id=46915;
> select * from tyu where object_id=46915
> *
> ERROR at line 1:
> ORA-01502: index 'JYANG.TYU_IDX' or partition of such index is in
> unusable
> state
>
>
> SQL> alter session set skip_unusable_indexes=true;
>
> Session altered.
>
> -- Does not work here
> SQL> select * from tyu where object_id=46915;
> select * from tyu where object_id=46915
> *
> ERROR at line 1:
> ORA-01502: index 'JYANG.TYU_IDX' or partition of such index is in
> unusable
> state
>
>
> SQL> alter session set skip_unusable_indexes=false;
>
> Session altered.
>
> 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 (maxvalue)
> 13 (subpartition p3_d1 values('A'),
> 14 subpartition p3_d2 values('B')
> 15 )
> 16 );
>
> Table created.
>
> SQL>
> -- Unique local prefixed partitioned index
> SQL> create unique index part_range_temp_idx on part_range_temp (c1,
> c3)
> 2 local;
>
> Index created.
>
> 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.
>
> 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.
>
> -- Usable index partition used, OK
> SQL> select * from part_range_temp where c1=50 and c3='B';
>
> C1 C2 C
> ---------- -- -
> 50 p1 B
>
> -- Failed when unusable partition accessed
> 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.
>
> -- Works now
> SQL> select * from part_range_temp where c1=50 and c3='A';
>
> C1 C2 C
> ---------- -- -
> 50 p1 A
>
> SQL>
>
> -- Can not insert, delete into unusable partition of course.
> SQL> insert into part_range_temp values(60,'p1','A');
> insert into part_range_temp values(60,'p1','A')
> *
> ERROR at line 1:
> ORA-01502: index 'JYANG.PART_RANGE_TEMP_IDX' or partition of such
> index is in
> unusable state
>
>
> SQL> delete from part_range_temp where c1=50 and c3='A';
> delete 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> insert into part_range_temp values(160,'p2','A');
>
> 1 row created.
>
> SQL>
>
>
> - Jusung Yang
Received on Mon May 26 2003 - 18:50:23 CDT