Analytic Functions and the Partitioning Clause
- From: "Brady, Mark" <Mark.Brady@xxxxxxxxxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 4 Nov 2008 13:03:53 -0500
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?
>>> This e-mail and any attachments are confidential, may contain legal,
professional or other privileged information, and are intended solely for the
addressee. If you are not the intended recipient, do not use the information
in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2
Other related posts:
