Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: CBO irregularity

From: Jonathan Lewis <>
Date: Tue, 8 Jun 2004 08:23:00 +0100
Message-ID: <029701c44d29$71aa7190$7102a8c0@Primary>

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.


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

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:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Tue Jun 08 2004 - 02:20:37 CDT

Original text of this message