Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: which should be faster

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 03 May 2003 11:26:37 -0800
Message-ID: <F001.0058EDEE.20030503112637@fatcity.com>

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

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'
and

    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).

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  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:
http://www.jlcomp.demon.co.uk/tutorial.html

____Denmark__May 21-23rd
____Sweden___June
____Finland__September
____Norway___September

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html

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

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

> Is the predicate order relavent for 8.1.7.3? Im doing quite a bit of
tuning
> and am wondering whether its worth the effort to reorganize the
predicate?
>
> 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: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US