Search notes:

Oracle SQL Plan operation HASH (GROUP BY PIVOT)

HASH (GROUP BY PIVOT) vs SORT AGGREGATE

A select statement with a pivot clause might employ a SORT AGGREGATE or a HASH GROUP BY PIVOT, apparently depending on the number of non-pivotized columns.
In order to demonstrate the difference, two tables are created, one with two columns, one with three columns: 
create table tq84_A (
   grp  char  (1  ),
   val  number(3,1)
);

create table tq84_B (
   grp  char  (1  ),
   cat  char  (1  ),
   val  number(3,1)
);
Data is inserted into the ables and the table statistics are gathered: 
begin
   insert into tq84_A values ('A',      66.1);
   insert into tq84_A values ('B',      20.2);
   insert into tq84_A values ('C',      74.8);
   insert into tq84_A values ('C',      52.3);
   insert into tq84_A values ('A',      26.8);
   insert into tq84_A values ('C',      43.3);
   insert into tq84_A values ('B',       7.1);
   insert into tq84_A values ('B',      25.2);

   insert into tq84_B values ('A', 'X', 66.1);
   insert into tq84_B values ('B', 'Y', 20.2);
   insert into tq84_B values ('C', 'Y', 74.8);
   insert into tq84_B values ('C', 'X', 52.3);
   insert into tq84_B values ('A', 'Y', 26.8);
   insert into tq84_B values ('C', 'X', 43.3);
   insert into tq84_B values ('B', 'X',  7.1);
   insert into tq84_B values ('B', 'X', 25.2);
  
   dbms_stats.gather_table_stats(user, 'tq84_B');
   dbms_stats.gather_table_stats(user, 'tq84_A');
end;
/
A query with a pivot clause is explained for each of the two tables and the execution plan displayed: 
explain plan for
select
   *
from
   tq84_A pivot (
      sum(val) for grp in ('A' sum_a, 'B' sum_b, 'C' sum_c)
   );

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

explain plan for
select
  *
from
   tq84_B pivot (
      sum(val) for grp in ('A' sum_a, 'B' sum_b, 'C' sum_c)
   );

select * from table(dbms_xplan.display(format=>'basic'));
--
-- --------------------------------------
-- | Id  | Operation           | Name   |
-- --------------------------------------
-- |   0 | SELECT STATEMENT    |        |
-- |   1 |  HASH GROUP BY PIVOT|        |
-- |   2 |   TABLE ACCESS FULL | TQ84_B |
-- --------------------------------------
Cleaning up: 
drop   table tq84_A;
drop   table tq84_B;

Another example

create table tq84_tab (
   val   number,
   grp   char(1),
   dim   number
);

insert into tq84_tab values (  7, 'A', 1);
insert into tq84_tab values (  5, 'B', 1);
insert into tq84_tab values (  2, 'C', 1);
insert into tq84_tab values (  4, 'C', 1);

insert into tq84_tab values (  1, 'A', 2);
insert into tq84_tab values (  1, 'B', 2);
insert into tq84_tab values (100, 'B', 2);
insert into tq84_tab values (  1, 'C', 2);

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

explain plan for
   select
      *
   from (
      select
         sum(val) sum_val,
         grp,
         dim
      from
         tq84_tab
      group by
         grp,
         dim
   
   )
   pivot (
       sum(sum_val) for grp in ('A', 'B', 'C')
   );

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

drop table tq84_tab;

See also

The plan operation HASH (GROUP BY).

Index