Re: Re: CBO irregularity

From: <ryan.gaffuri_at_cox.net>
Date: Tue, 8 Jun 2004 7:19:13 -0400
Message-Id: <20040608111911.NJJO9773.lakermmtao10.cox.net@smtp.central.cox.net>

questions in line...
>
> From: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
> Date: 2004/06/08 Tue AM 03:23:00 EDT
> To: <oracle-l_at_freelists.org>
> Subject: Re: CBO irregularity
>
>
> I think the word 'minority' is the critical word.
>
> The existence of nested loop with inner full tablescan
> is a necessity because it is usually the best way
> of performing a Cartesian join. (And a Cartesian
> join isn't inevitably that sin that people think it is -
> and they don't necessarily realise they are doing
> them because they can be performed without
> being reported in the execution plan).

I have seen Oracle use a cartesian join when I have a 3 table join and 2 tables are small and one is large. Oracle cartesian joins the two small tables and then hash joins them to the large table.

When else is it beneficial to have a cartesian join? When does Oracle do cartesian joins without 'telling' you?

>
> ORDERED is a usually a very good hint for a
> simple join if you know the business intent of
> the query. You tend to know the appropriate
> table order, and tell Oracle what it is. It is often
> an immediate winner.
>
> BUT it is extremely restrictive - it also has
> the unfortunate defect that it is applied only after
> subquery unnesting. Since 8i and 9i have
> different strategies for unnesting subqueries, the

I never quite understood what sub-query unnesting was. Could you explain it?

> same text with just the ordered hint may have
> dramatically different execution paths in the three
> versions. (I didn't mention 10g, because I haven't
> done any checks on its unnesting strategy - it may
> be different again: I do know that there are a couple
> of new spfile entries relating to unnesting subqueries).
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Jun 08 2004 - 06:16:04 CDT