Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re[2]: HI

Re[2]: HI

From: Jonathan Gennick <>
Date: Mon, 20 Sep 2004 11:36:48 -0400
Message-ID: <>

> 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:,F4950_P8_CRITERIA:11504677087008

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 * 906.387.1698 *

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit, or send email to and include the word "subscribe" in either the subject or body.

Monday, September 20, 2004, 10:43:52 AM, Jared Still ( wrote: JS> On Mon, 20 Sep 2004 15:41:34 +1000, Mark Richard JS> <> 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> --

Received on Mon Sep 20 2004 - 10:33:24 CDT

Original text of this message