Doh!! The problem is whether there is statistics on the table or not. It's that RBO/CBO issue. This feature (skip unusable indexes) needs stats.
To confirm it, I ran the following test on AIX 4.3.3 (should get same results on AIX 5L) Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production SQL> create table t1 as (select * from dba_tables); Table created. SQL> create index t1_ndx on t1 ( owner, table_name ); Index created. SQL> set autotrace on SQL> select owner, table_name from t1 where owner = 'DBM' and table_name like 'DBM_CUST%'; OWNER TABLE_NAME ------------------------------ ------------------------------ DBM DBM_CUSTOMERS Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 INDEX (RANGE SCAN) OF 'T1_NDX' (NON-UNIQUE) -- Statistics deleted to save e-mail length :) -- Table does not have any stats --- SQL> alter index t1_ndx unusable; Index altered. SQL> select owner, table_name from t1 where owner = 'DBM' and table_name like 'DBM_CUST%'; select owner, table_name from t1 where owner = 'DBM' and table_name like 'DBM_CUST%' * ERROR at line 1: ORA-01502: index 'SYSTEM.T1_NDX' or partition of such index is in unusable state SQL> alter session set skip_unusable_indexes = true; Session altered. SQL> select owner, table_name from t1 where owner = 'DBM' and table_name like 'DBM_CUST%'; select owner, table_name from t1 where owner = 'DBM' and table_name like 'DBM_CUST%' * ERROR at line 1: ORA-01502: index 'SYSTEM.T1_NDX' or partition of such index is in unusable state -- So, it appears that the feature does not work? -- Now, build stats on the table/index -- SQL> alter index t1_ndx rebuild; Index altered. SQL> analyze table t1 compute statistics; Table analyzed. SQL> alter index t1_ndx unusable; Index altered. SQL> select owner, table_name from t1 where owner = 'DBM' and table_name like 'DBM_CUST%'; OWNER TABLE_NAME ------------------------------ ------------------------------ DBM DBM_CUSTOMERS Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=4 Bytes=76) 1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=3 Card=4 Bytes=76) SQL> -- Now, Oracle knows that the index is unsable and it used FTS, as expected. HTH, - Kirti > -----Original Message----- > Sent: Thursday, May 29, 2003 8:20 PM > To: Multiple recipients of list ORACLE-L > > > hi > i am trying to figure out how unusable indexes could > help me in certain cases like bulk loading etc. i am > trying to understand how it works. > > i created a table with a index and used a query which > used this index. > > later i made this index unusable and unless and until > i make this index non-existent the query always > returns a 1502 error trying to access the table thru > the unusable index when i can see that full table scan > is still an option. the init.ora parameter > skip_unusable..is set up too. > > version is 9.2.0.3 on aix 5l. > > can someone clarify whether this is how it is supposed > to work or am i missing something . > > thanks > sai __________________________________ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
