Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!proxad.net!news.wiretrip.org!news2.arglkargh.de!news.n-ix.net!news.belwue.de!news.uni-stuttgart.de!carbon.eu.sun.com!btnet-feed5!btnet!news.btopenworld.com!not-for-mail From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server Subject: Re: Using Analytics...... Date: Fri, 3 Mar 2006 23:00:53 +0000 (UTC) Organization: BT Openworld Lines: 66 Message-ID: References: <1140780201.046227.240990@e56g2000cwe.googlegroups.com> <1141426139.359584.127770@e56g2000cwe.googlegroups.com> NNTP-Posting-Host: host86-130-246-17.range86-130.btcentralplus.com X-Trace: nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com 1141426853 4922 86.130.246.17 (3 Mar 2006 23:00:53 GMT) X-Complaints-To: news-complaints@lists.btinternet.com NNTP-Posting-Date: Fri, 3 Mar 2006 23:00:53 +0000 (UTC) X-RFC2646: Format=Flowed; Original X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 X-MSMail-Priority: Normal X-Priority: 3 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:262755 "Joel Garry" wrote in message news:1141426139.359584.127770@e56g2000cwe.googlegroups.com... > > Probably I'm misunderstanding something, but here is what I see with > autotrace on XE beta: > > ----------------------------------------------------------------------------- > > | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time > | > ----------------------------------------------------------------------------- > > | 0 | SELECT STATEMENT | | 24 | 1056 | 5 (40)| > 00:00:01 | > | 1 | HASH UNIQUE | | 24 | 1056 | 5 (40)| > 00:00:01 | > | 2 | VIEW | | 24 | 1056 | 4 (25)| > 00:00:01 | > | 3 | WINDOW SORT | | 24 | 528 | 4 (25)| > 00:00:01 | > | 4 | TABLE ACCESS FULL| T1 | 24 | 528 | 3 (0)| > 00:00:01 | > ----------------------------------------------------------------------------- > > Joel, The reason I said that the query was not "ordering" the data was that the OP was asking about data which had (apparently) already been ordered and he wanted to extract the first and last. However, my analytic example did not depend on the OP supplying the data in order, it simply took the raw data and handled it internally. You are correct - in my example. the query will order on the partitioning column, or columns, to get the job done. On the other hand, ordering by the partitioning columns may be a little cheaper than ordering by the partitioning columns AND the extra columns that the OP was considering. Moreover, if the OP had a data set that consisted of just one partition, then there would be no need for sorting in the window - although the distinct at the end could make that benefit vanish. -- Regards Jonathan Lewis http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html