WINDOW BUFFER
explain plan for
select
part,
num,
sum(num) over () cnt
from
tq84_tab;
select * from table(dbms_xplan.display(format=>'basic'));
--
-- ---------------------------------------
-- | Id | Operation | Name |
-- ---------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | WINDOW BUFFER | |
-- | 2 | TABLE ACCESS FULL| TQ84_TAB |
-- ---------------------------------------
WINDOW SORT
An analytic function with a partition by or order by clause requires the window operation with the sort option.
explain plan for
select
part,
num,
count(num) over (partition by part) cnt
from
tq84_tab;
select * from table(dbms_xplan.display(format=>'basic'));
--
-- ---------------------------------------
-- | Id | Operation | Name |
-- ---------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | WINDOW SORT | |
-- | 2 | TABLE ACCESS FULL| TQ84_TAB |
-- ---------------------------------------
explain plan for
select
part,
num,
sum(num) over (order by num) cnt
from
tq84_tab;
select * from table(dbms_xplan.display(format=>'basic'));
--
-- ---------------------------------------
-- | Id | Operation | Name |
-- ---------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | WINDOW SORT | |
-- | 2 | TABLE ACCESS FULL| TQ84_TAB |
-- ---------------------------------------
WINDOW SORT PUSHED RANK
The operation window requires a sort pushed rank operation if the analytic function rank() over () is used to in a subquery to limit the number of returned records.
The following query has a rank() over () analytic function, but it does not occur in a subquery. The operation is window, the option is sort:
explain plan for
select
t.*,
rank() over (order by t.num) rn
from
tq84_tab t;
select * from table(dbms_xplan.display(format=>'basic'));
--
-- ---------------------------------------
-- | Id | Operation | Name |
-- ---------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | WINDOW SORT | |
-- | 2 | TABLE ACCESS FULL| TQ84_TAB |
-- ---------------------------------------
However, if the previous query is used a subquery and the outer query uses the value returned by the rank() analytic function, the option of the window operation changes from sort to sort pushed rank:
explain plan for
select
part,
num
from (
select
t.*,
rank() over (order by t.num) rn
from
tq84_tab t
)
where
rn <= 42;
--
-- ---------------------------------------------
-- | Id | Operation | Name |
-- ---------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | VIEW | |
-- | 2 | WINDOW SORT PUSHED RANK| |
-- | 3 | TABLE ACCESS FULL | TQ84_TAB |
-- ---------------------------------------------