Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: which should be faster

Re: which should be faster

From: Jonathan Lewis <>
Date: Sat, 03 May 2003 11:26:37 -0800
Message-ID: <>

The statement is true in all versions
of Oracle up to 9, and includes Oracle 9 if you are not using the CPU_COSTING

However, the predicate ordering is significant only at what you might call the 'single table' level - which means the idea about inline views to apply filter predicates before joins is irrelevant. (Remember, Oracle tries quite hard to merge inline views, subqueries, and anything else it can, into a simple join).

If I am "n"tableX, then I will apply all available predicates against tableX that evaluate to constants at that point in time,before
joining to the next table. (i.e. things like

    tableX.col1 = 'const'

    tableX.col2 = {value from previously visited table}

        where col2 has not been used as an
        access path in the join.

Take a pair of predicates like:
            tableX.date_col >= trunc(sysdate)
    and qty = 99

If there are 1,000 rows to be examined, and one of them has qty = 99, but all of them have date_col >=trunc(sysdate), then the order as shown will require 1000 tests of date_col and 1,000 tests of qty.

In the opposite order, you need 1,000 tests of qty, and only one test of date_col -- which will be quicker. (Not a lot, but in a high concurrency system it all helps).

Oracle 9 with CPU costing on will use column selectivity figures, and fixed-operation costs to decide which order these two tests should be applied. Oracle 8.x will go top down.

(Come to think of it - I believe traditional CBO will go top down and RBO will go bottom up).


Jonathan Lewis

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:

____Denmark__May 21-23rd

Three-day seminar:

____UK_(Manchester)_May x 2
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ

> Is the predicate order relavent for Im doing quite a bit of
> and am wondering whether its worth the effort to reorganize the
> So is the rule of thumb on predicates.
> Filters before joins..
> If I have col1 = 'ABC', put that before my join?
> BTW, logically I would think it would help to put filters into
inline views,
> but Im not seeing any performance improvements. You have any luck
with this?

Please see the official ORACLE-L FAQ:
Author: Jonathan Lewis

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Sat May 03 2003 - 14:26:37 CDT

Original text of this message