Re: Re: CBO irregularity

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 8 Jun 2004 13:46:09 +0100

subquery unnesting example:

    select
            t1.*
    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
    where
        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).


Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


----- Original Message ----- 
From: <ryan.gaffuri@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, June 08, 2004 12:19 PM
Subject: Re: Re: CBO irregularity


: questions in line...
: >
: > From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
: > Date: 2004/06/08 Tue AM 03:23:00 EDT
: > To: <oracle-l@xxxxxxxxxxxxx>
: > 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: