Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Using Analytics......

Re: Using Analytics......

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 3 Mar 2006 23:00:53 +0000 (UTC)
Message-ID: <duahr5$4pq$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>

"Joel Garry" <joel-garry_at_home.com> wrote in message news:1141426139.359584.127770_at_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
Received on Fri Mar 03 2006 - 17:00:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US