| 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
"bocgco" <bocgco_at_bocgco.com> wrote in message news:<3ecd9889$1_at_shknews01>...
> Problem found, when there are subpartitions and some of the subpartitions'
> index are valid while some are unusable, the optimatizer will treat it as
> "usuable". However, when the enquery hits the unusable index, the problem
> will arise even "alter session set skip_unusable_index=true"
This is not true.
The status of an index can be evaluated at subpartition level. This is
evident by the view user_ind_subpartitions. Disbale an index
subpartition will not render the
entire index partition unusable. As long as you stay away from this
chunk of index, all queries should run. How do you stay away from this
chunk of index? You use full table scan when you try to get the rows
indexed by this unusable subpartition.
This is true regardless of the subpartitioning method. You can range-hash or range-list the table. It is all the same.
Here is a demo for range-list...
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>
 |
 |