Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Where clause order of operations?

Re: SQL Where clause order of operations?

From: T.Suresh Kumar <sureshkt_at_hotmail.com>
Date: Sat, 11 Jul 1998 10:44:47 +0100
Message-ID: <35A7340F.E01CA28A@hotmail.com>


Hi

    If you want to know that you can do it by placing the last 2 lines of your query within Parenthesis like

    ( s.sales_week     <= ai_weekboundary     and
       q.sales_week IS NULL;)

    As these Parenthesis got highest priority these 2 statements will be resolved first and then the table joins.

Try with this and also without parenthesis then analyse your query performance. If you want you can use PLAN_TABLE result or through tkprof utility. Before going for this through SQL*PLUS give set timing on and see the query performance.

I suppose it helps you.

Wishes
Suresh

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
Received on Sat Jul 11 1998 - 04:44:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US