第９回 2014.04.11
今回も前回に引き続きの担当に相成ってしまいますた…(>_<)
去年の年末からデータベースのお勉強シリーズでお送りしておりましたが
今回は「大人のSQL講座」と題してSQL文特にDMLについて深く掘り下げましょうって
感じで進めようと思っていました。
…まあ、結果として大人の階段を上るってぐらいの基本の「き」になってしまいましたが。
アジェンダとしては
- SQLとは
- DMLの基本構文
- ちょっと小洒落たSQL
- SQLの効率化・高速化のするには
みたいな感じです。
多少、大風呂敷を広げてしまった事を後悔しております(T_T)
SQLってパズルみたいな感覚なんですよね。
なもので、今回はクイズ形式のように進めていきました。
皆さん、ノートやPCにSQL文を書きながら小さく唸りつつ…。
ちょっとは頭の体操くらいにはなったかもしれません。
実際、聞くだけではなく、考えて手を動かすって良いもんですね。
相関副問い合わせについては、難しかったようで、
ワタクシも上手に説明できなくて反省しております(>_<)
まぁ、相関副問い合わせってループの様なイメージとかを持っていただければ
良いんですけど、パッと見？？ってなっちゃいますよね。
理解すると格段にSQLスキルはアップしますので、
ぜひ覚えて欲しいものです。
そして、質問コーナーで出ました逆順(降順)インデックスについて、
ちょっとその場では回答できないため、持ち帰りで調査をしてみましたが、
いろいろ興味深い結果になってきて、もうちょっと深く調べたくなってきましたので、
後日、調査結果報告をいたしますm(_ _)m
え〜お詫びですが、作ったスライドにてスペルをミスっちゃいました(>_<)
一旦アップしたんですが、間違えに気付いて、修正版を再アップしています。
という事でお詫びの印(?)として…
自己結合での相関副問い合わせはグルーピングされると
説明しましたが、その際に最大値のデータを取りたい等
お話も出てましたので、その辺りのパフォーマンスを調べてみましたよ！
事業所別の最大給与受給者の従業員コードと名前と給与額をとるイメージ
データベース：Oracle 11gR2 Express Edition
OS:Cent OS 6.5 (VirtualBox)
テーブルサンプル 約600万レコード
tab_c （サンプルテーブル 約600万レコード） 項目名 説明 備考 ------- --------------- --------------------------- empno 従業員コード 1からの連番 name 従業員名 deptno 事業所コード 1~13までランダムで入ってます num 給与額 データとしてはempnoと同値
[SQL]
自己結合を使った場合
select c1.empno,c1.name,c1.num from tab_c c1 where c1.num = (select max(c2.num) from tab_c c2 where c2.deptno = c1.deptno );
MAXを取るインラインビューで結合した場合
select c1.empno,c1.name,c1.num from tab_c c1, (select deptno,max(num) num from tab_c group by deptno) c2 where c1.deptno = c2.deptno and c1.num = c2.num ;
OLAP関数を使った場合
select e.empno,e.name,e.num from ( select empno,name,num,max(num) over(partition by deptno) maxnum from tab_c ) e where e.num = e.maxnum ;
[AUTO TRACE実行結果]
自己結合を使った場合
実行計画
----------------------------------------------------------
Plan hash value: 2010716975
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57677 | 3379K| 50227 (3) | 00:10:03 |
|* 1 | HASH JOIN | | 57677 | 3379K| 50227 (3) | 00:10:03 |
| 2 | VIEW | VW_SQ_1 | 13 | 338 | 25348 (4) | 00:05:05 |
| 3 | HASH GROUP BY | | 13 | 208 | 25348 (4) | 00:05:05 |
| 4 | TABLE ACCESS FULL| TAB_C | 5767K| 88M| 24828 (2) | 00:04:58 |
| 5 | TABLE ACCESS FULL | TAB_C | 5767K| 187M| 24828 (2) | 00:04:58 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"."NUM"="MAX(C2.NUM)" AND "ITEM_1"="C1"."DEPTNO")
統計
----------------------------------------------------------
138 recursive calls
0 db block gets
371818 consistent gets
185978 physical reads
0 redo size
1092 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
34 sorts (memory)
0 sorts (disk)
13 rows processed
MAXを取る内部ビューで結合した場合
実行計画
----------------------------------------------------------
Plan hash value: 595688616
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57677 | 3379K| 50227 (3)| 00:10:03 |
|* 1 | HASH JOIN | | 57677 | 3379K| 50227 (3)| 00:10:03 |
| 2 | VIEW | | 13 | 338 | 25348 (4)| 00:05:05 |
| 3 | HASH GROUP BY | | 13 | 208 | 25348 (4)| 00:05:05 |
| 4 | TABLE ACCESS FULL| TAB_C | 5767K | 88M| 24828 (2)| 00:04:58 |
| 5 | TABLE ACCESS FULL | TAB_C | 5767K | 187M| 24828 (2)| 00:04:58 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"."DEPTNO"="C2"."DEPTNO" AND "C1"."NUM"="C2"."NUM")
統計
----------------------------------------------------------
138 recursive calls
0 db block gets
371818 consistent gets
185978 physical reads
0 redo size
1092 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
34 sorts (memory)
0 sorts (disk)
13 rows processed
OLAP関数を使った場合
実行計画
----------------------------------------------------------
Plan hash value: 3486227064
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5767K| 280M | | 77679 (2)| 00:15:33 |
|* 1 | VIEW | | 5767K| 280M | | 77679 (2)| 00:15:33 |
| 2 | WINDOW SORT | | 5767K| 187M | 265M | 77679 (2)| 00:15:33 |
| 3 | TABLE ACCESS FULL| TAB_C | 5767K| 187M | | 24828 (2)| 00:04:58 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."NUM"="E"."MAXNUM")
統計
----------------------------------------------------------
294 recursive calls
26 db block gets
185964 consistent gets
170624 physical reads
0 redo size
1092 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
30 sorts (memory)
1 sorts (disk)
13 rows processedう〜ん…自己結合とインラインビューは全く同じ結果ですね〜。
OLAP関数は一番遅いですねwww
では、今度は特定の事業所での結果を確認してみます。
[SQL]
自己結合を使った場合
select c1.empno,c1.name,c1.num from tab_c c1 where c1.num = (select max(c2.num) from tab_c c2 where c2.deptno = c1.deptno ) and c1.deptno = 10 ;
MAXを取るインラインビューで結合した場合
select c1.empno,c1.name,c1.num from tab_c c1, (select deptno,max(num) num from tab_c where deptno = 10 group by deptno) c2 where c1.deptno = c2.deptno and c1.num = c2.num ;
select c1.empno,c1.name,c1.num from tab_c c1, (select deptno,max(num) num from tab_c group by deptno) c2 where c1.deptno = c2.deptno and c1.num = c2.num and c1.deptno = 10 ;
※今回の環境では同一の結果にしかなりませんでした(>_<)
OLAP関数を使った場合
select e.empno,e.name,e.num from ( select empno,name,num,max(num) over(partition by deptno) maxnum from tab_c where deptno = 10 ) e where e.num = e.maxnum ;
[AUTO TRACE実行結果]
自己結合を使った場合
実行計画
----------------------------------------------------------
Plan hash value: 3229880088
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4437 | 259K| 48471 (1)| 00:09:42 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 4437 | 259K| 48471 (1)| 00:09:42 |
| 3 | VIEW | VW_SQ_1 | 1 | 26 | 24759 (1)| 00:04:58 |
| 4 | HASH GROUP BY | | 1 | 16 | 24759 (1)| 00:04:58 |
|* 5 | TABLE ACCESS FULL | TAB_C | 443K| 6932K| 24759 (1)| 00:04:58 |
|* 6 | INDEX RANGE SCAN | IDX01 | 23693 | | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| TAB_C | 4437 | 147K| 23712 (1)| 00:04:45 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("C2"."DEPTNO"=10)
6 - access("C1"."NUM"="MAX(C2.NUM)")
7 - filter("C1"."DEPTNO"=10)
統計
----------------------------------------------------------
5 recursive calls
0 db block gets
185812 consistent gets
92991 physical reads
116 redo size
698 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
MAXを取るインラインビューで結合した場合
実行計画
----------------------------------------------------------
Plan hash value: 2458970658
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4437 | 259K| 48471 (1)| 00:09:42 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 4437 | 259K| 48471 (1)| 00:09:42 |
| 3 | VIEW | | 1 | 26 | 24759 (1)| 00:04:58 |
| 4 | HASH GROUP BY | | 1 | 16 | 24759 (1)| 00:04:58 |
|* 5 | TABLE ACCESS FULL | TAB_C | 443K| 6932K| 24759 (1)| 00:04:58 |
|* 6 | INDEX RANGE SCAN | IDX01 | 23693 | | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| TAB_C | 4437 | 147K| 23712 (1)| 00:04:45 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("DEPTNO"=10)
6 - access("C1"."NUM"="C2"."NUM")
7 - filter("C1"."DEPTNO"="C2"."DEPTNO")
統計
----------------------------------------------------------
1 recursive calls
0 db block gets
185808 consistent gets
92989 physical reads
0 redo size
698 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
OLAP関数を使った場合
実行計画
----------------------------------------------------------
Plan hash value: 2709613132
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 443K| 21M| 28821 (1)| 00:05:46 |
|* 1 | VIEW | | 443K| 21M| 28821 (1)| 00:05:46 |
| 2 | WINDOW BUFFER | | 443K| 14M| 28821 (1)| 00:05:46 |
|* 3 | TABLE ACCESS FULL| TAB_C | 443K| 14M| 24759 (1)| 00:04:58 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."NUM"="E"."MAXNUM")
3 - filter("DEPTNO"=10)
統計
----------------------------------------------------------
1 recursive calls
0 db block gets
185803 consistent gets
92989 physical reads
0 redo size
698 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
おぉぉ〜、OLAP関数が面目躍如ですねwww
Bytesとかは掛かっていますが、CostとTimeが他の２つの約半分です。
自己結合とインラインビューは相変わらず同じ実行結果ですwww
まぁ、今回はこのような結果になりましたが、
同じ値を取るSQLでも色々な書き方があり、それによって実行速度が変わるんだなぁ〜
と理解いただければ良いのかと(^^)
７月でこの勉強会も１周年ですね〜。
色々紆余曲折はありましたが、皆さんのお力添えあっての事だと思いますし、
やはり、継続していく事が大事なのかなと改めて思う次第であります。
では、次回はGW空け５月９日です！
