Re: Hints in Practice

Robert Klemme wrote:
> All,
>
> from what I read and from my limited personal experience, hints are
> superfluous most of the time. The general recommendation seems to be
> "make sure your schema is properly done, create appropriate indexes,
> make sure statistics are up to date and let the CBO do its work". Hints
> seem to be useful only in very rare cases (bugs?) where the CBO doesn't
> come up with an appropriate plan. Is this a correct summary of the
> situation or did I miss something?
>
> Thanks
>
> robert

I've personally used at least following hints: 1) all_rows and full - for an oltp app, that has also some reports. In db we have optimizer_mode first_rows_1 and this is absolutely inappropriate for these reports. I could probably manage with all_rows only.
2) cardinality - for functions for which default cardinality is magic 8168
3) leading - for at least 4-5 table join where I was _completely_ sure that it is the right table to start with. 4) first_rows(1) for queries which were opposite to case 1) For cases (1) and (4) I cannot see how for example altering optimizer_mode for session could be better than hint if I really do know that report has to scan all table or query will return no more than a few rows.
For case (2) I don't know any useful alternative. For case (3) I was too lazy to play around with statistics. All other times I've used hints just for quick fixes and they didn't remain in production. At least mostly :)

Gints Plivna
http://www.gplivna.eu Received on Sun Aug 27 2006 - 10:04:53 CDT