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

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 - 12:33:15 CDT