HASH GROUP BY PIVOT

pivot.sql

SQL> create table t as (select/*+ materialize */ decode(owner,'SYS','SYS','SYSTEM','SYSTEM','OTHERS') owner, object_type from dba_objects);

Table created.

SQL> explain plan for
  2  select *
  3  from t
  4  pivot (
  5  count(*) for owner in ('SYS','SYSTEM','OTHERS')
  6    );

Explained.

SQL> @xplan outline +alias

P_FORMAT
-----------------------------
typical outline +alias


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3924414983

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 76943 |  1352K|    61   (5)| 00:00:01 |
|   1 |  HASH GROUP BY PIVOT|      | 76943 |  1352K|    61   (5)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | T    | 76943 |  1352K|    58   (0)| 00:00:01 |
----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$58A6D7F6
   2 - SEL$58A6D7F6 / T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$58A6D7F6")
      FULL(@"SEL$58A6D7F6" "T"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$1" >"SEL$2")
      OUTLINE_LEAF(@"SEL$58A6D7F6")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)