Re[2]: HI

Jonathan Gennick <jonathan@xxxxxxxxxxx> · Mon, 20 Sep 2004 11:36:48 -0400

> Jonathan Gennick where are you? - care to comment?

LOL! Well, I certainly think I've delved more deeply into
subquery execution than I ever expected to :-)

Looking back at the original post, I find this question:

> I am having a very fundamental doubt, what is the order of
> retrival of the clauses when all are inclueded in the
> select stament

The SQL standard seems to define an ordering, but your
database's optimizer might surprise you. Srinivas, if you
haven't already, you might want to read the following
articles:

http://five.pairlist.net/pipermail/oracle-article/2004/000012.html

http://asktom.oracle.com/pls/ask/f?p=4950:8:3825101693476404515::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:11504677087008

http://www.dbdebunk.com/page/page/1351381.htm

I don't want to become embroiled again in any argument about
what is right or wrong. At this point, the important thing
is probably to understand what the standard has to say, to
understand that there is (or seems to be) disagreement over
the whole issue, and to understand that a given vender may
deviate from the standard for purposes of optimization.
You'll probably never have problems unless your tables are
poorly designed. The type-mismatch problem that began our
whole discussion was the result of what was probably a poor
design decision. I'll hedge just a bit, because I wasn't
around when that decision was taken.

I will also say this. I still find it helpful to work
through writing a query in a stepwise fashion, beginning
with the FROM clause. I don't know how others do it, but I
like to begin, conceptually at least, with my tables and
then work through the remaining clauses to refine the
results to those rows that I ultimately want.

Hope this helps.

Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:jonathan@xxxxxxxxxxx

Join the Oracle-article list and receive one
article on Oracle technologies per month by 
email. To join, visit http://five.pairlist.net/mailman/listinfo/oracle-article, 
or send email to Oracle-article-request@xxxxxxxxxxx and 
include the word "subscribe" in either the subject or body.

Monday, September 20, 2004, 10:43:52 AM, Jared Still (jkstill@xxxxxxxxx) wrote:
JS> On Mon, 20 Sep 2004 15:41:34 +1000, Mark Richard
JS> <mrichard@xxxxxxxxxxxxxxxxx> wrote:
>> 
>> The where clause must be processed first - since it removes atomic records
>> of data.  Next the group by has to be applied to determine the values of
>> max(sal).  This allows the having clause to be applied (which I tend to
>> think of as "where clauses applied after group by").  Finally the order by
>> can be applied to ensure the result set appears in the desired order.
>> 

JS> After recent discussions on this list regarding nested sub-queries, don't
JS> assume that Oracle will always process the WHERE clause first.

JS> (Check the archives )

JS> The simple query in the previous post won't be a problem, but it is 
JS> nonetheless a dangerous assumption.

JS> Jonathan Gennick where are you? - care to comment?

JS> Jared
JS> --
JS> //www.freelists.org/webpage/oracle-l

--
//www.freelists.org/webpage/oracle-l

Other related posts: