Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!e56g2000cwe.googlegroups.com!not-for-mail From: "Joel Garry" Newsgroups: comp.databases.oracle.server Subject: Re: Using Analytics...... Date: 3 Mar 2006 14:48:59 -0800 Organization: http://groups.google.com Lines: 167 Message-ID: <1141426139.359584.127770@e56g2000cwe.googlegroups.com> References: <1140780201.046227.240990@e56g2000cwe.googlegroups.com> NNTP-Posting-Host: 67.112.255.226 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1141426144 14183 127.0.0.1 (3 Mar 2006 22:49:04 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 3 Mar 2006 22:49:04 +0000 (UTC) In-Reply-To: User-Agent: G2/0.2 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0),gzip(gfe),gzip(gfe) X-HTTP-Via: 1.0 ISA-OC2 Complaints-To: groups-abuse@google.com Injection-Info: e56g2000cwe.googlegroups.com; posting-host=67.112.255.226; posting-account=YRNZ5wwAAAAg-yYjMFwy3FyWUbPiwNdq Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:262752 Jonathan: >Depends where and how you do your ordering, >but here's an example of the type of thing that >you could do. It partitions the data before >'ordering', but doesn't actually order it since >you only want the highest and lowest. Probably I'm misunderstanding something, but here is what I see with autotrace on XE beta: N1 FV LV CT ---------- --------- --------- ---------- 2 26-FEB-06 05-MAR-06 12 1 01-MAR-06 05-MAR-06 12 Execution Plan ---------------------------------------------------------- Plan hash value: 326228303 ----------------------------------------------------------------------------- | 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 | ----------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 627 bytes sent via SQL*Net to client 380 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2 rows processed N1 FV LV CT ---------- --------- --------- ---------- 2 26-FEB-06 05-MAR-06 12 1 01-MAR-06 05-MAR-06 12 Execution Plan ---------------------------------------------------------- Plan hash value: 326228303 ----------------------------------------------------------------------------- | 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 | ----------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 627 bytes sent via SQL*Net to client 380 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2 rows processed Isn't that "window sort" a form of ordering? Does the view-ishness of analytics force the full table scan as well as the sort? I can imagine with a much larger data set this might make a difference, if the intent is to skip some work. jg -- @home.com is bogus. Jury says Google sucks your data: http://www.silicon.com/ciojury/0,3800003161,39156914,00.htm