SQLで小計と合計を取得する:補足 (SQL: Subtotal and Grand Total: Appendix)

前回の記事で、処理性能の検証結果に「非効率なSQLの例」を載せ忘れていましたので掲載します。
また、group by および group by rollup()の例も併せて再掲します。

List 8:

********************************************************************************

select *

  from (

       select deptno, job, sum(sal) sum_sal

         from emp2

        group by deptno, job

       union

       select deptno, null as job, sum(sal) sum_sal

         from emp2

        group by deptno

       union

       select null as deptno, null as job, sum(sal) sum_sal

         from emp2

       )

 order by deptno, job

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.01          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      1.10       1.41      25695      50853          0          13

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      1.10       1.43      25695      50853          0          13

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 86  

Rows     Row Source Operation

-------  ---------------------------------------------------

     13  SORT ORDER BY (cr=50853 pr=25695 pw=0 time=0 us cost=1083 size=480 card=15)

     13   VIEW  (cr=50853 pr=25695 pw=0 time=120 us cost=1082 size=480 card=15)

     13    SORT UNIQUE (cr=50853 pr=25695 pw=0 time=36 us cost=1082 size=190 card=15)

     13     UNION-ALL  (cr=50853 pr=25695 pw=0 time=192 us)

      9      HASH GROUP BY (cr=16951 pr=8565 pw=0 time=32 us cost=248 size=165 card=11)

1200000       TABLE ACCESS FULL EMP2 (cr=16951 pr=8565 pw=0 time=2393598 us cost=239 size=1800000 card=120000)

      3      HASH GROUP BY (cr=16951 pr=8565 pw=0 time=202 us cost=248 size=21 card=3)

1200000       TABLE ACCESS FULL EMP2 (cr=16951 pr=8565 pw=0 time=2320510 us cost=239 size=840000 card=120000)

      1      SORT AGGREGATE (cr=16951 pr=8565 pw=0 time=0 us cost=587 size=4 card=1)

1200000       TABLE ACCESS FULL EMP2 (cr=16951 pr=8565 pw=0 time=1736062 us cost=239 size=480000 card=120000)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       2        0.00          0.00

  direct path read                                8        0.00          0.01

  asynch descriptor resize                        5        0.00          0.00

  SQL*Net message from client                     2        0.01          0.01

********************************************************************************

select deptno, job, sum(sal) sum_sal

  from emp2

 group by deptno, job

 order by

       deptno, job

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.54       0.53         15       8580          0           9

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      0.54       0.53         15       8580          0           9

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 86  

Rows     Row Source Operation

-------  ---------------------------------------------------

      9  SORT GROUP BY (cr=8580 pr=15 pw=0 time=0 us cost=284 size=135 card=9)

1200000   TABLE ACCESS FULL EMP2 (cr=8580 pr=15 pw=0 time=2297470 us cost=245 size=18000000 card=1200000)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       2        0.00          0.00

  db file sequential read                        15        0.00          0.00

  SQL*Net message from client                     2        0.00          0.00

********************************************************************************

select deptno, job, sum(sal) sum_sal

  from emp2

 group by rollup (deptno, job)

 order by

       deptno, job

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.54       0.56         17       8580          0          13

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      0.54       0.56         17       8580          0          13

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 86  

Rows     Row Source Operation

-------  ---------------------------------------------------

     13  SORT GROUP BY ROLLUP (cr=8580 pr=17 pw=0 time=0 us cost=284 size=195 card=13)

1200000   TABLE ACCESS FULL EMP2 (cr=8580 pr=17 pw=0 time=2089726 us cost=245 size=18000000 card=1200000)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       2        0.00          0.00

  db file sequential read                        17        0.00          0.00

  SQL*Net message from client                     2        0.01          0.01

---

[Summary]
I forgot to put the trece of Bad Example in the last post.
Here List 8 shows the results of three SQL - bad example, group by and group by rollup.