Getting ORA-01502 even with SKIP_UNUSABLE_INDEXES = TRUE

From: <vafanassiev_at_aapt.com.au>
Date: 6 Dec 2004 15:07:10 -0800
Message-ID: <1102374430.559971.107770@z14g2000cwz.googlegroups.com>

It should be possible to run SELECTS on
a table with an index in UNUSABLE state if SKIP_UNUSABLE_INDEXES = TRUE (at the session level). At least if the index is (1) non-unique and (2) not used to enforce a constraint. However, I am still getting ORA-01502:

Oracle 9.2.0.4.0 (32-bit) on Sun Solaris or Windows:

SQL>
SQL> create table test_table
2 (

3  	     n1      number,
4  	     n2      number,
5  	     n3      number

6 );

Table created.

SQL>
SQL> create index test_table_idx1 on test_table(n1);

Index created.

SQL>
SQL> select * from test_table where n1 = 1;

no rows selected

Execution Plan

---

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_TABLE' 2 1 INDEX (RANGE SCAN) OF 'TEST_TABLE_IDX1' (NON-UNIQUE) SQL>
SQL> alter index test_table_idx1 unusable;

Index altered.

SQL>
SQL> select * from test_table where n1 = 1; select * from test_table where n1 = 1
*

ERROR at line 1:
ORA-01502: index 'LPSROOT.TEST_TABLE_IDX1' or partition of such index is in
unusable state

SQL>
SQL> alter session set skip_unusable_indexes = true;

Session altered.

SQL>
SQL> select * from test_table where n1 = 1; select * from test_table where n1 = 1
*

ERROR at line 1:
ORA-01502: index 'LPSROOT.TEST_TABLE_IDX1' or partition of such index is in
unusable state

---

It works in 10g but not in earlier versions. Received on Mon Dec 06 2004 - 17:07:10 CST