Re: SQL puzzle

Interestingly, to help address this type of issue, there is an ORDERED_PREDICATES hint
in recent versions of Oracle that actually forces predicates to be evaluated in the order they appear - with the usual proviso "all other thing being equal".

i.e. once Oracle has decided on the optimum access path, any remaining predicates that do not affect the join path are evaluated in the order that they appear - although there is a table in the tuning guide that explains where certain factors affect precedence even after the path has been chosen.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



derf23456_at_my-deja.com wrote in message <94s4av$q14$1_at_nnrp1.deja.com>...


>As an Oracle consultant we had here explained, for a similar situation

>(a tuning question):

>

>The SQL statement is parsed and pushed onto a stack.  When the

>optimizer evaluates the code, it pulls the code back off the stack

>(which will be from end to start).

>

>This might expaain why (it seems) ver. 1 works and ver. 2 doesn't

>(assuming that the code is also executed off the stack).

>

>Even if this isn't the CORRECT reason, it sure sounds good (IMHO:-).

>