Re: Analytic Functions and the Partitioning Clause

Mark,

Very interesting question. My gut feeling would be to say that over (), like count(*), is a kind of "special case". If you specify neither partitioning nor ordering, it's probably logical to expect the value for the last row returned for which it isn't null, whatever it is, somewhat like rownum = 1 gives you the first row returned, whatever it is.

I vote for regular behavior.

SF

Brady, Mark wrote:
> Right now in 10gR2 these two queries results are identical.
> select ROW_PRIORITY, last_value(col1 ignore nulls) over () col1
> from (SELECT * FROM ztest ORDER BY ROW_PRIORITY );
> select ROW_PRIORITY,
> last_value(col1 ignore nulls) over (ORDER BY ROW_PRIORITY ROWS BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) col1
> from (SELECT * FROM ZTEST);
> It seems to me that the first one shouldn’t be guaranteed to always
> perform as such. That it would be possible that the optimizer would
> eliminate the sort of an order by in the middle of query. Or is the
> existence of the analytics reason enough for it to never do that…
> knowing that it could affect the outcome.
> select * from (
> select * from (
> select * from all_objects_main order by owner desc
> ) order by owner
> ) order by owner desc
> From what I can tell, there’s only one sort happening here… so the CBO
> will eliminate those superfluous sorts. Would you say that the first
> query above is a bug waiting to happen or is that guaranteed behavior?

--
http://www.freelists.org/webpage/oracle-l