Re: CBO irregularity
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 8 Jun 2004 08:23:00 +0100
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).
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
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).
Even the INDEX hint can break - under 8i,
index(t1 i1 i2)
means 'use one of these indexes'. Under 9i (and
this may be an accident) the hint could result in
Oracle using both indexes in an index_combine
after a btree-bitmap conversion.
Even the poor old FULL() hint could cause trouble -
like making a nested loop use an inner table full scan.
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: "Binley Lim" <Binley.Lim@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, June 08, 2004 2:41 AM
Subject: Re: CBO irregularity
In practice, I had to resort to these "plan-restricting" hints in a small
minority of cases, especially where partitioning is involved.
I don't use RULE, except to see what effect it has.
I don't use USE_NL because I often get nested-loop of inner
full-table-scans, usually if an index is missing. If the index is really
missing, a hash-join is almost-always a better option. Even without the
hint, the CBO does choose NL of inner FTS by itself (more in 9i than
previous releases). I think this particular combination should be outlawed
by the CBO because it doesn't make sense. I would be glad to hear feedback
on this.
I would not classify ORDERED as "restricting". It is very useful if you know
the data distribution, and allows you to start with the most selective order
(still true of 9i). This selective order should hold true for future
releases, unless Oracle comes up with a newer join-method (the last great
one was HASH!).
INDEX is sometimes required, in conjunction with the other hints.
FULL, I use with PARALLEL Adjusting the (parallel degree) is what I consider
an art.
.
----------------------------------------------------------------
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:
