Re: SQL Where clause order of operations?

In article <6o5u92$akb$1_at_nnrp1.dejanews.com>,   drpaner_at_intrex.net wrote:
> I have an SQL insert statement that is designed to find Sales records that do
> not have a corresponding Quota record. From that result set the
statement is
> to insert a corresponding 'dummy' Quota record for any orphaned Sales
> records.
>
> 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.
>
> This statement accomplishes that successfully.
>
> INSERT INTO INC_QUOTA
> SELECT S.SALES_YEAR, S.SALES_WEEK, S.TERRITORY_CODE,
> S.BRAND_CODE, 0.0
> FROM inc_sales_summary s, inc_quota q
> WHERE s.sales_week = q.sales_week (+) and
> s.sales_year = q.sales_year (+) and
> s.territory_code = q.territory_code (+) and
> s.brand_code = q.brand_code (+) and
> s.sales_week <= ai_weekboundary and
> q.sales_week IS NULL;
>
> 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.
>
> I've heard this argument before but have not been able to verify if either one
> of these scenarios is the correct scenario.
>
> Question: How does Oracle interpret the Where clause criteria? Are table
> joins completed first or are non-table joins completed first?
>
> Any insight into this would be greatly appreciated!
> Daniel
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>

If you are using Cost-based optimizer, the order does not really matter.

If you are using Rule-based optimizer, things get really complicated. ROB will use the driving table and types of join to optimise your query, and the way your order your "From" and "Where" clause really matter but subject to other factors like indexes, out-join, operators/functions..etc. The combination of factors will actually determine your execution path which will show up in your Explain path. So it's worth your while to read up Oracle tuning books and tell your co-workers that ROB is harder to predict than merely re-arranging the order.

Briefly, in your case, ROB will choose the table that is not an outer-join as the driving table so inc_sales_summary is used as the driving table. Assuming that s.sales_week is indexed. s.sales_week <= a i_weekboundary should show up as the inner-most nested loop or s.sales is NULL for full table scan. Rearranging in this case, should not make a big difference except if you swap the last 2 conditions.

Hope this helps.

Alan Lee
BSI Consulting
Houston, Texas

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Jul 13 1998 - 17:37:08 CDT