Search notes:

Oracle SQL Plan operation SORT AGGREGATE

The sort aggregate operation is used when aggregate functions are applied to an entire table/subquery (i.e. without group by).
Despite the name, sort aggregate does not involve a sort operation.
Compare with BUFFER (SORT) and HASH GROUP BY. 
create table tq84_tab (
   val number,
   grp varchar2(5)
);

explain plan for
   select count(*) from tq84_tab
-- select max(grp) from tq84_tab
-- select avg(val) from tq84_tab
;

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


drop table tq84_tab;

KEEP … DENSE_RANK

SORT AGGREGATE is also used for SQL statements that are using KEEP … DENSE_RANK: 
drop   table tq84_tab;
create table tq84_tab (
   id     integer,
   grp    varchar2(3),
   val    number
);


begin
insert into tq84_tab values (1, 'AAA',  7);
insert into tq84_tab values (2, 'AAA', 12);
insert into tq84_tab values (3, 'AAA',  5);
insert into tq84_tab values (4, 'BBB',  2);
insert into tq84_tab values (5, 'CCC', 21);
insert into tq84_tab values (6, 'CCC', 18);
insert into tq84_tab values (7, 'CCC',  3);
end;
/


explain plan for
   select max(id) keep (dense_rank first order by val desc)
from
   tq84_tab;


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

See also

group by
Plan operations

Index