Oracle分组函数之高效的ROLLUP(三)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 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
㈤ 再看一个ROLLUP两列的情况
[sql]
hr@ORCL> select department_id,job_title,sum(salary) from rollup_test group by rollup(department_id,job_title);
DEPARTMENT_ID JOB_TITLE SUM(SALARY)
------------- ----------------------------------- -----------
Sales Representative 7000
7000
10 Administration Assistant 4400
10 4400
20 Marketing Manager 13000
20 Marketing Representative 6000
20 19000
30 Purchasing Clerk 13900
30 Purchasing Manager 11000
30 24900
40 Human Resources Representative 6500
40 6500
50 Stock Clerk 55700
50 Stock Manager 36400
50 Shipping Clerk 64300
50 156400
60 Programmer 28800
60 28800
70 Public Relations Representative 10000
80 Sales Manager 61000
80 Sales Representative 243500
80 304500
90 President 24000
90 Administration Vice President 34000
90 58000
100 Accountant 39600
100 Finance Manager 12000
100 51600
110 Public Accountant 8300
110 Accounting Manager 12000
110 20300
691400
33 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 3210238927
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 4815 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY ROLLUP| | 107 | 4815 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL | ROLLUP_TEST | 107 | 4815 | 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