Re: Re: CBO irregularity
- From: <ryan.gaffuri@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 8 Jun 2004 7:19:13 -0400
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:
