Oracle 9i以后，扩展了group by 的功能，能够满足大部分多维数据的分析统计功能，主要表现：
1. rollup,cube,grouping sets 扩展group by字句提供了丰富的多维分组统计功能；
2. 3个扩展分组函数：grouping,grouping_id,group_id提供扩展group by的辅助功能:提供区别结果行属于哪个分组级别，区分NULL值，建立有意义的报表，对汇总结果排序，过滤结果行等；
3.对扩展group by允许按重复列分组，组合列分组，连接分组等，另外，grouping sets可以接受cube,rollup操作作为参数。
ROLLUP
没有rollup的union all功能
SQL> select a.dname, b.job, sum(b.sal) sum_sal
from scott.dept a, scott.emp b
where a.deptno = b.deptno
group by a.dname, b.job
union all
select a.dname, NULL, sum(b.sa l) sum_sal
from scott.dept a, scott.emp b
where a.deptno = b.deptno
group by a.dnam e
union all
select null, null, sum(b.sal) sum_sal
from scott.dept a, scott.emp b wh ere a.deptno = b.deptno;
显示结果：
DNAME JOB SUM_SAL
-------------- --------- ----------
SALES MANAGER 2850
SALES CLERK 950
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING CLERK 1300
SALES SALESMAN 5600
RESEARCH MANAGER 2975
RESEARCH ANALYST 6000
RESEARCH CLERK 1900
ACCOUNTING 8750
RESEARCH 10875 DNAME JOB SUM_SAL
-------------- --------- ----------
SALES 9400
29025 已选择13行。
执行计划
----------------------------------------------------------
Plan hash value: 3113041979 --------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 19 | 479 | 13 (70)|
00:00:01 |
| 1 | UNION-ALL | | | | |
|
| 2 | HASH GROUP BY | | 14 | 392 | 5 (20)|
00:00:01 |
| 3 | NESTED LOOPS | | 14 | 392 | 4 (0)|
00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)|
00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)|
00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
00:00:01 |
| 7 | HASH GROUP BY | | 4 | 80 | 5 (20)|
00:00:01 |
| 8 | NESTED LOOPS | | 14 | 280 | 4 (0)|
00:00:01 |
| 9 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)|
00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)|
00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
00:00:01 |
| 12 | SORT AGGREGATE | | 1 | 7 | |
|
|* 13 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)|
00:00:01 |
--------------------------------------------------------------------------------
---------- Predicate Information (identified by operation id):
--------------------------------------------------- 6 - access("A"."DEPTNO"="B"."DEPTNO")
11 - access("A"."DEPTNO"="B"."DEPTNO")
13 - filter("B"."DEPTNO" IS NOT NULL) 统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
53 consistent gets
0 physical reads
0 redo size
780 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed
可以看到，需要多次访问emp，dept表以及dept表的索引。
修改成：
select a.dname, b.job, sum(b.sal) sum_sal
from scott.dept a, scott.emp b
where a.deptno = b.deptno
group by rollup(a.dname, b.job);
结果如下：
DNAME JOB SUM_SAL
-------------- --------- ----------
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
SALES 9400
RESEARCH CLERK 1900
RESEARCH ANALYST 6000
RESEARCH MANAGER 2975
RESEARCH 10875
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000 DNAME JOB SUM_SAL
-------------- --------- ----------
ACCOUNTING 8750
29025执行计划
----------------------------------------------------------
Plan hash value: 503922295 --------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 14 | 392 | 5 (20)| 0
0:00:01 |
| 1 | SORT GROUP BY ROLLUP | | 14 | 392 | 5 (20)| 0
0:00:01 |
| 2 | NESTED LOOPS | | 14 | 392 | 4 (0)| 0
0:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 0
0:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 0
0:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
--------- Predicate Information (identified by operation id):
--------------------------------------------------- 5 - access("A"."DEPTNO"="B"."DEPTNO") 统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
750 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13 rows processed已选择13行。
可见，使用rollup只需要访问emp表一次，通过dept表的主键获得rowid表查询相关行，比union all性能要好。