Re: Unusable index in Partitioned table

Anyway, to solve the unusable state of an index partition problem you should first find the index partitions that are in unusable state: SELECT TABLE_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
WHERE STATUS='UNUSABLE';
and rebuild those partitions individually: ALTER INDEX <INDEX NAME> REBUILD PARTITION <PARTITION NAME>; Finally make sure you have updated statistics from that partitions, for instance by doing:
ANALYZE INDEX <INDEX NAME> PARTITION (<PARTITION NAME>) ESTIMATE STATISTICS;

Hope this helps,

Yago Hernandez

"Jusung Yang" <JusungYang_at_yahoo.com> wrote in message news:130ba93a.0305230959.66ed8827_at_posting.google.com...
> "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>
>
>
> - Jusung Yang
Received on Sat May 24 2003 - 16:47:05 CDT