RE: unusable indexes.
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 -- -- DBMDBM_CUSTOMERS Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 10 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 -- -- DBMDBM_CUSTOMERS Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=4 Bytes=76) 10 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).
RE: unusable indexes.
I think the problem is skip_unusable... isn't an init.ora parameter. At least it wasn't in earlier versions. You can set it at session level 'alter session set skip_unusable_indexes...'. I ended up adding it to a logon trigger to make it affect all sessions. HTH. -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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sai Selvaganesan 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seefelt, Beth 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).
Re: unusable indexes.
If the index is a unique index, then ORA-1502 is not suppressed. If not, it surely looks like a bug. File an iTAR, and let us know how OWS addresses it. - Kirti --- Sai Selvaganesan <[EMAIL PROTECTED]> wrote: > 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).
RE: Unusable indexes : why ?
Title: RE: Unusable indexes : why ? Stephane, I had this happen to me previously. Did you by chance use an /*+ APPEND */ hint? That was another possible culprit that was mentioned to me. Did you move the table ? ALTER TABLE ... MOVE ... ?? Lisa Koivu Data Bored Administrator Ft. Lauderdale, FL, USA "The dba that impersonates directors" -Original Message- From: paquette stephane [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, July 11, 2001 12:27 PM To: Multiple recipients of list ORACLE-L Subject: Unusable indexes : why ? Hi, Oracle 816, I just had 13 unusable indexes (btree and bitmap). I have no partition and no sql*loader load so how come those 13 indexes are unusable ? = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Pour faire vos courses sur le Net, Yahoo! Shopping : http://fr.shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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).
Re: Unusable indexes : why ?
Did anyone do alter table <> MOVE ? --- paquette stephane <[EMAIL PROTECTED]> wrote: > Hi, > > Oracle 816, I just had 13 unusable indexes (btree > and > bitmap). > I have no partition and no sql*loader load so how > come > those 13 indexes are unusable ? > > > > = > Stéphane Paquette > DBA Oracle, consultant entrepôt de données > Oracle DBA, datawarehouse consultant > [EMAIL PROTECTED] > > ___ > Do You Yahoo!? -- Pour faire vos courses sur le Net, > > Yahoo! Shopping : http://fr.shopping.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: =?iso-8859-1?q?paquette=20stephane?= > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > 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). __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sakthi , Raj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Unusable indexes : why ?
Did you move the tables around? -Original Message- Sent: Wednesday, July 11, 2001 12:27 PM To: Multiple recipients of list ORACLE-L Hi, Oracle 816, I just had 13 unusable indexes (btree and bitmap). I have no partition and no sql*loader load so how come those 13 indexes are unusable ? = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Pour faire vos courses sur le Net, Yahoo! Shopping : http://fr.shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Unusable indexes : why ?
Move any tables via ALTER TABLE ... MOVE recently? > -Original Message- > From: stephane.paquette [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, July 11, 2001 11:27 AM > To: ORACLE-L; stephane.paquette > Subject: Unusable indexes : why ? > > > Hi, > > Oracle 816, I just had 13 unusable indexes (btree and > bitmap). > I have no partition and no sql*loader load so how come > those 13 indexes are unusable ? > > > > = > Stéphane Paquette > DBA Oracle, consultant entrepôt de données > Oracle DBA, datawarehouse consultant > [EMAIL PROTECTED] > > ___ > Do You Yahoo!? -- Pour faire vos courses sur le Net, > Yahoo! Shopping : http://fr.shopping.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: =?iso-8859-1?q?paquette=20stephane?= > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).