| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re[2]: HI
just to add my two cents, without the intention to open that discussion
again ;-)
if you have a SQL statement containing WHERE, GROUP BY, and HAVING clauses,
it would theoretically still be possible to do things in any order, I guess,
but I am 99% sure that any optimizer would first evaluate the WHERE clause
before the GROUP BY. and obviously, the HAVING must be processed after the
GROUP BY.
By the way, I think we should get rid of the GROUP BY alltogether -- SQL has
much more powerful and elegant constructs to achieve the same results. think
about correlated subqueries in the SELECT clause. But that might open up the
discussion again about whether subquery merging is acceptable under all
circumstances ... and I didn't want to go there ;-)
additions/corrections welcome,
Kind regards,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jonathan Gennick
Sent: Monday, September 20, 2004 16:37
To: Jared Still
Cc: mrichard_at_transurban.com.au; srinivast_at_kpitcummins.com;
oracle-l_at_freelists.org
Subject: Re[2]: HI
> 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_P 8_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@gennick.com
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_at_gennick.com and
include the word "subscribe" in either the subject or body.
Monday, September 20, 2004, 10:43:52 AM, Jared Still (jkstill_at_gmail.com)
wrote:
JS> On Mon, 20 Sep 2004 15:41:34 +1000, Mark Richard
JS> <mrichard_at_transurban.com.au> 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> http://www.freelists.org/webpage/oracle-l
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 20 2004 - 10:59:36 CDT
 |
 |