Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Using Analytics......
"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.htmlReceived on Fri Mar 03 2006 - 17:00:53 CST