| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: which should be faster
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
 |
 |