用SCOTT/TIGER登录。
ROLLUP,是GROUP BY子句的一种扩展,可以为每个分组返回小计记录以及为所有分组返回总计记录。
表EMP所有数据:
SELECT * FROM EMP;
结果:
不用ROLLUP:
SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB;
结果:
执行计划:
SQL> SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB;
JOB SUM(SAL)
--------- ----------
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000
执行计划
----------------------------------------------------------
Plan hash value: 4067220884
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 60 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 5 | 60 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 168 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
567 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)
5 rows processed
使用ROLLUP:
SELECT JOB, SUM(SAL) FROM EMP GROUP BY ROLLUP(JOB);
结果:
执行计划:
SQL> SELECT JOB, SUM(SAL) FROM EMP GROUP BY ROLLUP(JOB);
JOB SUM(SAL)
--------- ----------
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
29025
已选择6行。
执行计划
----------------------------------------------------------
Plan hash value: 52302870
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 60 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY ROLLUP| | 5 | 60 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 168 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
578 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)
6 rows processed