Oracle分组函数之高效的ROLLUP(二)
(SALARY)
------------- -----------
10 4400
20 19000
30 24900
40 6500
50 156400
60 28800
70 10000
80 304500
90 58000
100 51600
110 20300
7000
691400
13 rows selected.
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 3210238927
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 2782 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY ROLLUP| | 107 | 2782 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL | ROLLUP_TEST | 107 | 2782 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
648 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
㈣ 使用Group By语句翻译一下上面的SQL语句如下（union all一个统计所有数据的行）
[sql]
hr@ORCL> select department_id,sum(salary) from rollup_test group by department_id
2 union all
3 select null, sum(salary) from rollup_test
4 order by 1;
DEPARTMENT_ID SUM(SALARY)
------------- -----------
10 4400
20 19000
30 24900
40 6500
50 156400
60 28800
70 10000
80 304500
90 58000
100 51600
110 20300
7000
691400
13 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 1519347417
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 108 | 2795 | 8 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 108 | 2795 | 7 (58)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | HASH GROUP BY | | 107 | 2782 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| ROLLUP_TEST | 107 | 2782 | 3 (0)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 13 | | |
| 6 | TABLE ACCESS FULL| ROLLUP_TEST | 107 | 1391 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement