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

If I'm not mistaken, this explanation is absolutely correct!

Regards,

--
David C. Sisk
Need tech info on Oracle? Visit The Unofficial ORACLE on NT site at http://www.ipass.net/~davesisk/oont.htm Like original music? Listen to song samples and buy a CD at http://www.mp3.com/disparityofcult

rspeaker_at_my-deja.com wrote in message <7peqoq$8kv$1_at_nnrp1.deja.com>...
>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.
>
>Any thoughts on this from the Oracle experts here?
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Wed Aug 18 1999 - 21:51:30 CDT