Re: Q: Conditions order in WHERE clause

> Does somebody know in what order are WHERE
> conditions evaluated? Is it determined or is the final
> decision made by an optimizer?
>
> E.g.:
> select ...
> from ...
> where A1.A = 5 -- c1
> and A1.B = A2.B -- c2
> and A1.C = A3.C (+) -- c3
> and A2.D is NULL -- c4
>
> Is this evaluated as it is written? :
> c1 first then c2, c3 and finally c4?
>
> How to ensure join conditions be evaluated first?

If you are using cost-based optimization then the order of evaluation is irrelevant because whatever you write is passed through the optimizer and re-arranged as necessary. The cost-based optimizer only comes into play if statistics are collected on the tables.

If you are using rule-based optimization then the statements are handled top-down. However, when using rule-based optimization the order of tables listed in the FROM clause becomes important since they are evaluated from the bottom up. If you use EXPLAIN PLAN you should be able to determine the best order for any given sql statement. Keep in mind that even if you specify CHOOSE (or accept it as the default) in the initxxx.ora file, you will always use rule-based optimization unless you collect statistics with ANALYZE TABLE. Received on Wed Jul 28 1999 - 10:04:42 CDT