Re: Unusable partition index -- working funny
I would check which index is being reported as unusable, and check the access path for the query when all indexes are useable. Since you have a statement level trigger, I suspect Oracle is producing an execution plan that dictate the use of index X. The plan executes, which means the trigger fires, but the execution engine is committed to using index X - which happens to be unusable, so the statement fails. On the second call, the session parameters have changed, so Oracle re-parses the update, and ignores the unusable index, choosing a different plan. Consequently the update can work. In the case where the index being used to access the data is useable (i.e. where only the index on the updated column is unusable), I would assume that Oracle makes the decision about updating indexes only after columns have been updated, therefore it can notice the effect of session switch in mid-statement. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, January 22, 2004 12:19 AM > Thanks for your reply Jonathan..Here is an update.. > The update that i sent you yesterday is updating a column on which there > is a local bitmap index. There are also other local bitmsap indexes on > that partitions. Yesterday i made all the local indexes pertaining to > that partition UNUSABLE and we got the results that i posted > yesterday..Today i went and made all the indexes usable and then made > only the local index on the column which we are updating unusable while > the rest of the local bitmap indexes were usable. and then the update > stmt was run. There was NO problem at all. It ran the first time without > giving the error of index being in the unusable state. That nmeans the > trigger has fired. So what would be the explanation in this case. > If i make only that local bitmap index unusable, it works ok but if i > make all the local bitmap indexes unusable in that partition, we get the > situation that i posted yesterday.. > > Thanks, > > Sathish. > > > SQL> connect [EMAIL PROTECTED] > > > Enter password: ** > > > Connected. > > > SQL> UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = > > > 1 where > > > 2 mortgage_loan_key = 1 and period_key = '30-JUN-03'; > > > UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = > > > 1166444 where > > > * > > > ERROR at line 1: > > > ORA-01502: index 'NEVADMIN.DM_MORTGAGE_LOAN_HIST_BK13' or partition of > > > such > > > index is in unusable state > > > > > > SQL> / > > > > > > 1 row updated. > - > > On Wed, 21 Jan 2004 00:24:25 -0800, "Jonathan Lewis" > <[EMAIL PROTECTED]> said: > > > > It's probably the case that the trigger fires > > the first time - but at parse/optimise time > > Oracle had already determined the sequence > > of actions needed to execute the statement > > based on the then session state, so that sequence > > is played out, irrespective of the fact that you > > changed the session state in the middle of > > the sequence. > > > > By analogy, consider an update to an > > updatable join view which defaults to > > using a hash join. If you create a "before > > row update" trigger to disable hash joins, > > would you expect Oracle to not do a hash > > join the first time the statement executes ? > > > > > > Regards > > > > Jonathan Lewis > > http://www.jlcomp.demon.co.uk > > > > The educated person is not the person > > who can answer the questions, but the > > person who can question the answers -- T. Schick Jr > > > > > > Next public appearance2: > > March 2004 Hotsos Symposium - Keynote > > March 2004 Charlotte NC - OUG Tutorial > > April 2004 Iceland > > > > > > One-day tutorials: > > http://www.jlcomp.demon.co.uk/tutorial.html > > > > > > Three-day seminar: > > see http://www.jlcomp.demon.co.uk/seminar.html > > UK___February > > > > > > The Co-operative Oracle Users' FAQ > > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > > > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Wednesday, January 21, 2004 12:19 AM > > > > > > > Hello All, > > > I have a strange problem... > > > I have a table on which i am doing an update. Its a partition table and > > > the local index on the column which is being updated is in
Re: Unusable partition index -- working funny
Thanks for your reply Jonathan..Here is an update.. The update that i sent you yesterday is updating a column on which there is a local bitmap index. There are also other local bitmsap indexes on that partitions. Yesterday i made all the local indexes pertaining to that partition UNUSABLE and we got the results that i posted yesterday..Today i went and made all the indexes usable and then made only the local index on the column which we are updating unusable while the rest of the local bitmap indexes were usable. and then the update stmt was run. There was NO problem at all. It ran the first time without giving the error of index being in the unusable state. That nmeans the trigger has fired. So what would be the explanation in this case. If i make only that local bitmap index unusable, it works ok but if i make all the local bitmap indexes unusable in that partition, we get the situation that i posted yesterday.. Thanks, Sathish. SQL> connect [EMAIL PROTECTED] > > Enter password: ** > > Connected. > > SQL> UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = > > 1 where > > 2 mortgage_loan_key = 1 and period_key = '30-JUN-03'; > > UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = > > 1166444 where > > * > > ERROR at line 1: > > ORA-01502: index 'NEVADMIN.DM_MORTGAGE_LOAN_HIST_BK13' or partition of > > such > > index is in unusable state > > > > SQL> / > > > > 1 row updated. - On Wed, 21 Jan 2004 00:24:25 -0800, "Jonathan Lewis" <[EMAIL PROTECTED]> said: > > It's probably the case that the trigger fires > the first time - but at parse/optimise time > Oracle had already determined the sequence > of actions needed to execute the statement > based on the then session state, so that sequence > is played out, irrespective of the fact that you > changed the session state in the middle of > the sequence. > > By analogy, consider an update to an > updatable join view which defaults to > using a hash join. If you create a "before > row update" trigger to disable hash joins, > would you expect Oracle to not do a hash > join the first time the statement executes ? > > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > The educated person is not the person > who can answer the questions, but the > person who can question the answers -- T. Schick Jr > > > Next public appearance2: > March 2004 Hotsos Symposium - Keynote > March 2004 Charlotte NC - OUG Tutorial > April 2004 Iceland > > > One-day tutorials: > http://www.jlcomp.demon.co.uk/tutorial.html > > > Three-day seminar: > see http://www.jlcomp.demon.co.uk/seminar.html > UK___February > > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Wednesday, January 21, 2004 12:19 AM > > > > Hello All, > > I have a strange problem... > > I have a table on which i am doing an update. Its a partition table and > > the local index on the column which is being updated is in an unusable > > state. > > I have a database trigger at statement level (before update of col_a for > > ) where i do an execute immediate ' alter session set > > skip_unusable_indexes = true'; > > > > i log into sqlplus as the owner of the table and do the following > > > > > > SQL> connect [EMAIL PROTECTED] > > Enter password: ** > > Connected. > > SQL> UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = > > 1 where > > 2 mortgage_loan_key = 1 and period_key = '30-JUN-03'; > > UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = > > 1166444 where > > * > > ERROR at line 1: > > ORA-01502: index 'NEVADMIN.DM_MORTGAGE_LOAN_HIST_BK13' or partition of > > such > > index is in unusable state > > > > SQL> / > > > > 1 row updated. > > -- > - > > > > My question is why does the trigger not fire for the first time... > > When i do the / i am able to update the table which means the trigger is > > firing the 2nd time. > > > > Any help would be greatly appriciated.. > > > > thanks, > > > > sathish. > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jonathan Lewis > 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
Re: Unusable partition index -- working funny
It's probably the case that the trigger fires the first time - but at parse/optimise time Oracle had already determined the sequence of actions needed to execute the statement based on the then session state, so that sequence is played out, irrespective of the fact that you changed the session state in the middle of the sequence. By analogy, consider an update to an updatable join view which defaults to using a hash join. If you create a "before row update" trigger to disable hash joins, would you expect Oracle to not do a hash join the first time the statement executes ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 21, 2004 12:19 AM > Hello All, > I have a strange problem... > I have a table on which i am doing an update. Its a partition table and > the local index on the column which is being updated is in an unusable > state. > I have a database trigger at statement level (before update of col_a for > ) where i do an execute immediate ' alter session set > skip_unusable_indexes = true'; > > i log into sqlplus as the owner of the table and do the following > > > SQL> connect [EMAIL PROTECTED] > Enter password: ** > Connected. > SQL> UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = > 1 where > 2 mortgage_loan_key = 1 and period_key = '30-JUN-03'; > UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = > 1166444 where > * > ERROR at line 1: > ORA-01502: index 'NEVADMIN.DM_MORTGAGE_LOAN_HIST_BK13' or partition of > such > index is in unusable state > > SQL> / > > 1 row updated. > -- - > > My question is why does the trigger not fire for the first time... > When i do the / i am able to update the table which means the trigger is > firing the 2nd time. > > Any help would be greatly appriciated.. > > thanks, > > sathish. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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 partition index -- working funny
My guess it's firing the first time but is not taking effect during the current transaction may be because it fires as a recursive sql within the main sql. Not a good idea to put this in a trigger. Regards, Waleed -Original Message- Sent: Tuesday, January 20, 2004 7:19 PM To: Multiple recipients of list ORACLE-L Hello All, I have a strange problem... I have a table on which i am doing an update. Its a partition table and the local index on the column which is being updated is in an unusable state. I have a database trigger at statement level (before update of col_a for ) where i do an execute immediate ' alter session set skip_unusable_indexes = true'; i log into sqlplus as the owner of the table and do the following SQL> connect [EMAIL PROTECTED] Enter password: ** Connected. SQL> UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = 1 where 2 mortgage_loan_key = 1 and period_key = '30-JUN-03'; UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = 1166444 where * ERROR at line 1: ORA-01502: index 'NEVADMIN.DM_MORTGAGE_LOAN_HIST_BK13' or partition of such index is in unusable state SQL> / 1 row updated. --- My question is why does the trigger not fire for the first time... When i do the / i am able to update the table which means the trigger is firing the 2nd time. Any help would be greatly appriciated.. thanks, sathish. -- http://www.fastmail.fm - Consolidate POP email and Hotmail in one place -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Khedr, Waleed 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).
