From oracle-l-bounce@freelists.org Mon Sep 20 00:39:21 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i8K5dKE02251 for ; Mon, 20 Sep 2004 00:39:20 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i8K5dKI02246 for ; Mon, 20 Sep 2004 00:39:20 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1484272C282; Mon, 20 Sep 2004 00:45:20 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 04348-24; Mon, 20 Sep 2004 00:45:19 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6E05272C2FE; Mon, 20 Sep 2004 00:45:19 -0500 (EST) Subject: Re: HI To: SrinivasT@KPITCummins.com Cc: oracle-l@freelists.org Message-ID: From: Mark Richard Date: Mon, 20 Sep 2004 15:41:34 +1000 X-MIMETrack: Serialize by Router on DMZNOTESVR1/TRANSURBAN (Release 6.0.3|September 26, 2003) at 20/09/2004 03:42:45 PM MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-archive-position: 9758 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mrichard@transurban.com.au Precedence: normal Reply-To: mrichard@transurban.com.au X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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" mins.com> cc: Sent by: Subject: HI oracle-l-bounce@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