Re: HI

From: Mark Richard <mrichard_at_transurban.com.au>
Date: Mon, 20 Sep 2004 15:41:34 +1000
Message-ID: <OFE19A1A7F.0CB1A3DF-ONCA256F15.001F4161@transurban.com.au>

Hi,

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.

The trick of course is that Oracle is smart... In the process of applying the group by it has effectively accomplished the order by clause so it most likely won't execute another sort procedure. There are many scenario's where Oracle can accomplish multiple tasks at once and simplify the processing. But in terms of logical application - the order I mention above is correct.

Regards,

Mark.

                                                                                                                                       
                      "Srinivas T"                                                                                                     
                      <SrinivasT_at_KPITCum        To:       <oracle-l_at_freelists.org>                                                     
                      mins.com>                 cc:                                                                                    
                      Sent by:                  Subject:  HI                                                                           
                      oracle-l-bounce_at_fr                                                                                               
                      eelists.org                                                                                                      
                                                                                                                                       
                                                                                                                                       
                      20/09/2004 15:28                                                                                                 
                      Please respond to                                                                                                
                      SrinivasT

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

Where
Group by
having
order by.

eg : select deptno,max(sal) from emp where job like 'SAL%' group by deptno having max(sal)>1000 order by deptno

Regards,
Srinivas

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







<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone.
In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them.


<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>


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

Received on Mon Sep 20 2004 - 00:39:21 CDT