| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: CBO irregularity
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/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
: 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?
:: > subquery unnesting. Since 8i and 9i have
: >
: > 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
:: > of new spfile entries relating to unnesting subqueries).
: > 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
-- 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 - 07:43:11 CDT
 |
 |