Will the Optimizer override Hints?

[Andrew McLellan]

Hi Gang,
I'm in the process of tuning some queries in a new data warehouse on 8.1.6
and I have a handful of queries that are not using the indexes even when
a hint is added. I was wondering if the Optimizer will ignore indexes and
hints where the cost is not high enough? In one instance there are 56423
records out of 564224 being return. I would assume this is a high enough
ratio for an index to be used. Any help would be greatly appreciated.
Regards
Andrew

[Boris Milrud]

Andrew,

1. Update statistics.
2. Make sure the index you specify in a hint is appropriate for that query.
3. Make sure your hint syntax is correct: right table alias, etc.

Cost-based optimizer usually uses index specified in a hint, even though
full table scan would be a better path. Just for the test purposes: I ran
the query that selected 874 rows out of 934. That surely resulted in full
table scan. After I added a hint to use the index, optimizer followed my
advice and switched to index range scan.

Boris Milrud.


"Andrew McLellan" <mclellan_a@nospamYahoo.com> wrote:
>
>Hi Gang,
>I'm in the process of tuning some queries in a new data warehouse on 8.1.6
>and I have a handful of queries that are not using the indexes even when
>a hint is added. I was wondering if the Optimizer will ignore indexes and
>hints where the cost is not high enough? In one instance there are 56423
>records out of 564224 being return. I would assume this is a high enough
>ratio for an index to be used. Any help would be greatly appreciated.
>Regards
>Andrew