Oracle-L: RE: unusable indexes.

From: Kirtikumar Deshpande <kirtikumar_deshpande_at_yahoo.com>
Date: Fri, 30 May 2003 07:59:42 -0800
Message-ID: <F001.005A6F74.20030530075942@fatcity.com>

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: kirtikumar_deshpande_at_yahoo.com

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: ListGuru_at_fatcity.com (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).

Received on Fri May 30 2003 - 10:59:42 CDT