How To Force Oracle To Use Index On The Update Statement???

[learning_bee]

if oracle doesn't pick up the index on the update statement, what else can you do to force Oracle to use index beside the hint????
thanks

[davey23uk]

give it some good stats - have you done that. There is always the chance that using an index is not the best idea

[learning_bee]

I have

I run into the issue refer to my other threads, I have two instances exactly the same on structure with slighttly difference on data. the other database oracle pick up the index fine and this instance doesn't pick the index. I use oracle hint on other code but for update statement you can't use hint from what I understand.

any ideas???

[jmodic]

Where do you get the idea that you can only use hints for SELECT statements? You can use hints for UPDATE and DELETE statements (and one hint for INSERTs even!) as well as well.

[learning_bee]

I am not clear on this, could someone please explain to me. If I use Oracle hint and it will pick up the index, how come the cost is much higher than the cost if oracle do FTS???? I read about first_rows hint and all_rows hint and I am not too clear, how come first_rows sometime worst than all_rows cost????

thanks

[sysdba]

Comparing cost of two queries make no sense.

[learning_bee]

could you please explain???? I am trying to lean

thanks

[slimdave]

Originally posted by learning_bee
I have two instances exactly the same on structure with slightly difference on data.

It's not just differences in the data that matter. Other issues that Oracle considers are ...

* The order of the data (as judged by the clustering factor on the index)
* What the statistics say about the index and the table, as opposed to what the actual data is.
* Values of initialization parameters.
* System statistics, if you have gathered them.

A good place to start learning would be to read the Oracle Performance Guide from http://tahiti.oracle.com, and the Concepts Guide.