Hello,
I just became aware of hints last month and haven't found much documentation on them. Most seem to advise against using them even when doing a search here and finding this topic.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7038986332061 Then I went to the last session at Open World yesterday and saw them in many of your queries. Why was that? Feel free to point me somewhere if you've answered this before or can point me to some additional documentation on the subject.
Thank you,
Bill
When I ran my queries - I was demonstrating a concept - you are probably referring to when I was showing the clustering factor - what is it, how it works, what it means.
I stated "the clustering factor is a measure of how many IO's it would take to read every indexed record from the table via the index". In order to *show* that, I had to a) show the clustering factor from the dictionary and then b) read every indexed record from the table via that index in order to c) show the direct correlation between the two.
Now, to read every record from the table via a given index (especially an index that has a 'bad' clustering factor), is something the optimizer would not normally do on it's own. So, I used a hint to force it's hand - to make the optimizer read every row via the index.
As you saw, the resulting query performance was horrible - we did the maximum IO possible, the optimizer would NEVER use that plan - but I needed to use that plan to demonstrate the concept.
I like to say there are two kinds of hints - good hints and bad hints.
Good hints give the optimizer MORE INFORMATION - things like first_rows_n, that is a good hint, it tells the optimizer to optimize for initial response time. all_rows - that is a good hint, it tells the optimizer to optimize for total throughput.
Bad hints tell the optimizer what to do, use this index, process the tables in this order, use this join technique. I do not like to use those in real code. I use them in *examples* to show how something works - but not to tune with - in general.
So, yesterday - when you saw me doing the hints, it was to demonstrate how clustering factors work, what they mean and why Oracle will stop using your index in a range scan after a fairly small set of rows if the clustering factor is "bad"
The example in question was similar to this one:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9422487749968#14135826006483