SQL> explain plan for SELECT o.owner, o.object_type, COUNT(*) 2 FROM all_my_objects o 3 GROUP BY o.object_type, o.owner 4 UNION ALL 5 SELECT NULL, o.object_type, COUNT(*) 6 FROM all_my_objects o 7 GROUP BY o.object_type 8 UNION ALL 9 SELECT o.owner, NULL, COUNT(*) 10 FROM all_my_objects o 11 GROUP BY o.owner 12 UNION ALL 13 SELECT NULL, NULL, COUNT(*) 14 FROM all_my_objects o; Explained. SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'all')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2152443955 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1017K| 18M| 6597 (76)| 00:01:20 | | 1 | UNION-ALL | | | | | | | 2 | HASH GROUP BY | | 339K| 9272K| 1653 (2)| 00:00:20 | | 3 | TABLE ACCESS FULL| ALL_MY_OBJECTS | 339K| 9272K| 1640 (1)| 00:00:20 | | 4 | HASH GROUP BY | | 339K| 3642K| 1653 (2)| 00:00:20 | | 5 | TABLE ACCESS FULL| ALL_MY_OBJECTS | 339K| 3642K| 1640 (1)| 00:00:20 | | 6 | HASH GROUP BY | | 339K| 5629K| 1652 (2)| 00:00:20 | | 7 | TABLE ACCESS FULL| ALL_MY_OBJECTS | 339K| 5629K| 1638 (1)| 00:00:20 | | 8 | SORT AGGREGATE | | 1 | | | | | 9 | TABLE ACCESS FULL| ALL_MY_OBJECTS | 339K| | 1638 (1)| 00:00:20 | Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$1 2 - SEL$1 3 - SEL$1 / O@SEL$1 4 - SEL$2 5 - SEL$2 / O@SEL$2 6 - SEL$3 7 - SEL$3 / O@SEL$3 8 - SEL$4 9 - SEL$4 / O@SEL$4 Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - STRDEF[30], STRDEF[19], STRDEF[22] 2 - (#keys=2) "O"."OBJECT_TYPE"[VARCHAR2,19], "O"."OWNER"[VARCHAR2,30], COUNT(*)[22] 3 - "O"."OWNER"[VARCHAR2,30], "O"."OBJECT_TYPE"[VARCHAR2,19] 4 - (#keys=1) "O"."OBJECT_TYPE"[VARCHAR2,19], COUNT(*)[22] 5 - "O"."OBJECT_TYPE"[VARCHAR2,19] 6 - (#keys=1) "O"."OWNER"[VARCHAR2,30], COUNT(*)[22] 7 - "O"."OWNER"[VARCHAR2,30] 8 - (#keys=0) COUNT(*)[22] Note ----- - dynamic sampling used for this statement