Search notes:

Oracle SQL Plan operation: WINDOW (SORT)

The SQL plan operation WINDOW (SORT) is used in SQL statements with analytic functions whose OVER() clause has either a partition by clause or an order by clause.

If the OVER() clause had neither clause, Oracle would use the WINDOW (BUFFER) plan operation.

create table tq84_A (
   pt   varchar2(1),
   nm   number
);

explain plan for
select
   pt,
   nm,
   MAX(NM) OVER (PARTITION BY PT)
from
   tq84_A;

select * from table(dbms_xplan.display(format=>'basic'));
--
-- -------------------------------------
-- | Id  | Operation          | Name   |
-- -------------------------------------
-- |   0 | SELECT STATEMENT   |        |
-- |   1 |  WINDOW SORT       |        |
-- |   2 |   TABLE ACCESS FULL| TQ84_A |
-- -------------------------------------


explain plan for
select
   pt,
   nm,
   RANK() OVER(ORDER BY NM)
from
   tq84_A;

select * from table(dbms_xplan.display(format=>'basic'));
--
-- -------------------------------------
-- | Id  | Operation          | Name   |
-- -------------------------------------
-- |   0 | SELECT STATEMENT   |        |
-- |   1 |  WINDOW SORT       |        |
-- |   2 |   TABLE ACCESS FULL| TQ84_A |
-- -------------------------------------

drop table tq84_A;

Oracle SQL Plan operation: WINDOW (SORT)

See also