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" <ORACLE-L_at_fatcity.com> 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 nevadmin_at_nv_prod
> > > Enter password: ******
> > > Connected.
> > > SQL> UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY
=
> > > 11111 where
> > > 2 mortgage_loan_key = 11111 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"
> <jonathan_at_jlcomp.demon.co.uk> 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" <ORACLE-L_at_fatcity.com>
> > 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 nevadmin_at_nv_prod
> > > Enter password: ******
> > > Connected.
> > > SQL> UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY
=
> > > 11111 where
> > > 2 mortgage_loan_key = 11111 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: jonathan_at_jlcomp.demon.co.uk
> >
> > 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).
>
> --
> http://www.fastmail.fm - Send your email first class
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: sat0789_at_fastmail.fm
>
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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