Search notes:

Oracle SQL Plan operation HASH (GROUP BY)

drop table tq84_tab;

create table tq84_tab (
   val  number,
   grp  number,
   data varchar2(100)
);

insert into tq84_tab
select
   dbms_random.value(1,10),
   trunc(dbms_random.value(1, 10)),
   rpad('x', 100, 'x')
from
   dual connect by level <= 100000
;

begin
   dbms_stats.gather_table_stats(user, 'tq84_tab');
end;
/


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

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

See also

The plan operations
group by

Index