Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: CBO irregularity

Re: Re: CBO irregularity

From: <>
Date: Tue, 8 Jun 2004 7:19:13 -0400
Message-Id: <>

questions in line...
> From: "Jonathan Lewis" <>
> Date: 2004/06/08 Tue AM 03:23:00 EDT
> To: <>
> 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:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Tue Jun 08 2004 - 06:16:04 CDT

Original text of this message