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