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.
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.
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 ?
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).
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 ?
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 ?
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).
