Re: Unusable index in Partitioned table

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 21 May 2003 11:15:51 -0700
Message-ID: <130ba93a.0305211015.1b54347b@posting.google.com>

"bocgco" <bocgco_at_bocgco.com> wrote in message news:<3ecafb4e$1_at_shknews01>...
> In Oracle 9.2.0, solaris platform, I have encountered the following problem.
>
> As the data is loaded thru direct path loading and the index has been set
> unusable during the load. When I use select statement, the error occurred
> even in sqlplus.
>
> ERROR at line 1:
> ORA-01502: index 'MYDB.MYDATA_IDXL01' or partition of such index is in
> unusable state
>
> I have exec "alter session set unusable index=true" already but it still
> can't work. Finally I found that if all partitons' index has been set
> unusable then it will be fine. Oracle will be able to do a table scan. The
> problem occurs when only some of the partitioned index of the same table has
> been set to unusable.
>
> Do anyone have similar experience or have workaround for me ?

You mean "alter session set skip_unusable_indexes=ture", of course? If not, correct it and it should work. Yours is a local index, yes? You will only get ORA-01502 when the optimizer tries to access the unusable portion of the index to get you the data. If the optimizer use full table scan, you will not get error. If the optimizer uses only the usable partitions of the index, you will not get error. Here is an example using local prefixed index...

SQL> create table part_range_temp (c1 number(3), c2 varchar2(2)) 2 partition by range (c1)
3 (partition p1 values less than (100), 4 partition p2 values less than (200), 5 partition p3 values less than (maxvalue));

Table created.

SQL>
SQL> create index part_range_temp_idx on part_range_temp (c1) 2 local;

Index created.

SQL>
SQL> insert into part_range_temp values(50, 'p1');

1 row created.

SQL> insert into part_range_temp values(150, 'p2');

1 row created.

SQL> insert into part_range_temp values(250, 'p3');

1 row created.

SQL> alter index part_range_temp_idx modify partition p2 unusable;

Index altered.

SQL>
SQL> set autotrace on explain

Index not used, no problem SQL> select * from part_range_temp;

C1 C2
---------- --

        50 p1
       150 p2
       250 p3

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1227 Bytes=19
          632)

   1    0   PARTITION RANGE (ALL)
   2    1     TABLE ACCESS (FULL) OF 'PART_RANGE_TEMP' (Cost=3 Card=12
          27 Bytes=19632)

Usable partition of the index accessed, no problem SQL> select * from part_range_temp where c1=50;

C1 C2
---------- --

50 p1

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=64) 1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'PART_RANGE_TEMP' ( Cost=1 Card=4 Bytes=64)

   2    1     INDEX (RANGE SCAN) OF 'PART_RANGE_TEMP_IDX' (NON-UNIQUE)
           (Cost=1 Card=2)

Unusable partition of the index accessed, trouble SQL> select * from part_range_temp where c1=150; select * from part_range_temp where c1=150 * 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.

Optimizer avoided index scan, OK now SQL> select * from part_range_temp where c1=150;

C1 C2
---------- --

150 p2

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=64) 1 0 TABLE ACCESS (FULL) OF 'PART_RANGE_TEMP' (Cost=2 Card=4 By

tes=64)

The usable partitions of the index remain available to the optimizer. SQL> select * from part_range_temp where c1=50;

C1 C2
---------- --

50 p1

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=64) 1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'PART_RANGE_TEMP' ( Cost=1 Card=4 Bytes=64)

   2    1     INDEX (RANGE SCAN) OF 'PART_RANGE_TEMP_IDX' (NON-UNIQUE)
           (Cost=1 Card=2)

SQL>

Jusung Yang

Received on Wed May 21 2003 - 13:15:51 CDT