Re: Interesting Information ... BUT is it correct, or not?

rspeaker_at_my-deja.com wrote:

> okay, here is one for the Oracle gurus. I recently attended a
> presentation by one of Quest Software's lead technical reps. He made
> an interesting statement, which he 'proved' via one of their tools (I
> don't remember which one), but I'd like to know if anybody has ever
> seen anything to confirm or refute this. I'll try to recap as best as
> I remember.
>
> If the optimizer mode is set to rule-based, but a query is issued with
> a hint, such as /*+ First_Rows */, the query will use cost-based
> optimization. If the table the hinted query is run against has not
> been analyzed, and thus has no statistics, Oracle will "make up" the
> statistics for the number of rows, maxing out at approximately the
> block size. The rep claims to have seen a situation where a user was
> joining a small reference table with a large detail table (1MM
> records), and was having performance problems on the query. Supposedly
> the Quest tool performed an explain plan against the statement, and
> showed a small number of rows in the large table. Since Oracle was
> only identifying the table as having a small number of rows (4072 I
> think) instead of over 1 million, it was performing a full table scan
> on this large table which was causing the problem. The rep said the
> solution, assuming you always want to use rule-based optimization, was
> to set ruled-based in the init.ora file, instead of choose, and analyze
> the table(s) in question. This would allow the cost-based-optimizer,
> forced by the hint, to use the actual table statistics instead of
> guessing, and would have no adverse effect on the database since rule-
> based was being forced.

If this is true then it certainly seems to be quite different than the behavior as documented by Oracle.

At the same time, if it is true, then it would provide a way of ensuring that cost-based optimization is used as long at the hint is available

However, I've also heard from a variety of sources that there is no guarantee that hints will always be supported since they effectively violate FIPS and the ISO standards.

But, my interest is piqued. I'm anxious to hear the thoughts of others.

Ken