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