Re: SQL Where clause order of operations?

On Fri, 10 Jul 1998 20:41:03 GMT, drpaner_at_intrex.net wrote:

>The question concerns the way in which Oracle 7.3 interprets the Where clause.
>My understanding is that all table joins will be completed and then any
>non-table joins criteria will 'filter' out the appropriate records.

Your best bet is to use the EXPLAIN PLAN command and have Oracle's optimizer tell you exactly how the query will be executed. EXPLAIN PLAN is documented in the Server SQL Reference manual.

>However, there is a debate going among my colleagues that the final two lines
>'s.sales_week <= ai_weekboundary' and 'q.sales_week IS NULL' should be
>interpretted before any of the table joins as it could potentially make the
>table joins smaller and faster be removing undesired records.

People get into these "it should do this" type of argument all the time. I've known people to spend more time debating what they think the optimizer could or should do than it would take to just find out in the first place. Don't philosophize, just find out. Use EXPLAIN PLAN.

If you don't like what the optimizer is doing, you can use "hints" to force certain execution plans, so based on your knowledge of the data, you can tell the optimizer to use a specific index, or a join tables in a specific order, etc.

regards,

Jonathan Received on Sat Jul 11 1998 - 23:38:42 CDT