| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Unusable index in Partitioned table
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.
Index created.
SQL> select * from tyu where rownum <2;
OBJECT_ID
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.
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.
C1 C2 C
---------- -- -
50 p1 B
SQL> alter session set skip_unusable_indexes=true;
Session altered.
C1 C2 C
---------- -- -
50 p1 A
SQL>
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>
 |
 |