Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: Re: CBO irregularity

From: Jonathan Lewis <>
Date: Tue, 8 Jun 2004 13:46:09 +0100
Message-ID: <031b01c44d56$95ea1a10$7102a8c0@Primary>

subquery unnesting example:


    from table

        where t1.col1 in (
            select t2.co1
            from t2
            where t2.col2 like 'adfasdfas'

rewritten internally as

    select t1.*

        (select distinct t2.co11 from t2
           where t2.col2 like 'adfasdfas'
        ) v1,
        t1.col1 = v1.col1

There are various conditions that apply, but basically there are cases where subqueries can be rewritten in some way and turned into an inline view in the main query.

Possibly these should be renamed Gaja'd
queries, in honour of the person who first published this as a possibly strategy for improving subquery performance. (It doesn't always help, by the way, which is why the uncosted unnesting that appears in 9i has caused problems for a few people).

Cartestion Joins:

    I have an example lurking somewhere of     Oracle join the results of two indexes access     using a cartesian join without shoing the     word (CARTESIAN). I'll see if I can find     it.

    Cartesian joins appear if the optimizer thinks     they are the fastest option - as with all features     of the optimizer, it's not always obvious why     the code should have been written in a way     which makes a cartesian join sensible - your     example is a good one, or course, I can't     think of any other classes which would     'obviously' contain good candidates. (Apart     from the extension to 4 or more tables).


Jonathan Lewis The Co-operative Oracle Users' FAQ Optimising Oracle Seminar - schedule updated May 1st

: 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

: > 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 - 07:43:11 CDT

Original text of this message