Analytic Functions and the Partitioning Clause
Date: Tue, 4 Nov 2008 13:03:53 -0500
Message-ID: <1BB5C9CF81E4FB4F8FFED437AA3E5D634A62ED3917@EXM-OMF-02.Ceg.Corp.Net>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 04 2008 - 12:03:53 CST