1. SVC34

    No comment

    SVC34
Changes in body
Source | HTML | Preview
@@ -1,202 +1,202 @@
DB2 for LUW 11.1よりSQL中にヒント(最適化ガイドライン)を記述できるようになりました。
# 11.1以前
DB2 for LUWでは従来V9.1より実行計画を制御するための「最適化プロファイル」機能が提供されていました(後にV8.2にバックポート)。[^1]しかし、これを利用するには以下のようにやや煩雑な手続きが必要でした。
- 対象のSQL文とそれに対するヒント(最適化ガイドライン)をXMLで記述した最適化プロファイルを作成する
- SYSTOOLS.OPT_PROFILE表を作成し、作成した最適化プロファイルをINSERTする
- 作成した最適化プロファイルの使用をSQL実行時に宣言する
その他の方法として統計情報の数字を手動で更新して固定するという裏技がありますが、これは複雑なSQLを意図した実行計画に誘導するのが難しく、またすべてのSQLに影響が出るためチューニング対象でないSQLの実行計画が悪化する懸念もあります。
なお、DB2 for z/OSでは「ヒント」という名称で実行計画制御機能が提供されていますが、XMLを用いないことを除き利用方法は最適化プロファイルに類似しており、やはりSQL中には指定できません。
# 11.1での改良点
DB2 for LUW 11.1では最適化ガイドラインをSQL中に記述し、他のRDBMSのヒント機能と同じように利用することが可能になりました。SQL中の最適化ガイドラインは以下のようにブロックコメント/* */内に記述します。
```sql
SELECT * FROM table WHERE
/* <OPTGUIDELINES><IXSCAN TABLE='table' INDEX="index"/></OPTGUIDELINES> */
```
やはりXMLでの記述が必要な点でDB屋にとってはまだ少しとっつきにくいですが、これでずいぶん導入しやすくなりました。
最適化プロファイルのXMLスキーマはsqllibディレクトリ下のmisc/DB2OptProfile.xsdで提供されています。
# 試してみた
## 表の準備
以下のようなEMPLOYEE表とDEPARTMENT表を用意します。EMPLOYEE表には従業員の所属部門を示すDEPTID、DEPARTMENT表には部門のマネージャを示すMANAGERIDが設定されていますが、どちらも参照制約は設定していません。
```
create table employee(id int not null, name varchar(32) not null, deptid int)
alter table employee add constraint pk_employee primary key(id)
create index ix_employee_deptid on employee(deptid)
create table department(id int not null, name varchar(32) not null, managerid int)
alter table department add constraint pk_department primary key(id)
create index ix_department_managerid on department(managerid)
```
## 例1 - 索引の指定
-以下のようにIDとDEPTIDが指定されたEMPLOYEE表に対するSQLをEXPLAINにかけると、索引PK_EMPLOYEE(PRIMARY KEY制約付与時に自動的に作成されたもの)が使用されていました。
+以下のようにIDとDEPTIDが指定されたEMPLOYEE表に対するSQLをEXPLAINにかけると、索引IX_EMPLOYEE_DEPTIDが使用されていました。
```sql
select * from employee where id < 1000 and deptid = 20
```
```
- Rows
- RETURN
- ( 1)
- Cost
- I/O
- |
- 2.10667
- FETCH
- ( 2)
- 6.84511
- 1
- /----+----\
- 6.32 158
- IXSCAN TABLE: SVC34
- ( 3) EMPLOYEE
- 0.0314387 Q1
- 0
- |
- 158
- INDEX: SVC34
- PK_EMPLOYEE
- Q1
-```
-
-PK_EMPLOYEEではなくIX_EMPLOYEE_DEPTIDを使用してもらいたい場合、以下のように最適化ガイドラインを記述します。IXSCAN要素が索引スキャンの指示であり、TABLE属性に対象の表、INDEX属性に使用する索引を指定します。
-
-```xml
-select * from employee where id < 1000 and deptid = 20
-/*
-<OPTGUIDELINES>
- <IXSCAN TABLE='employee' INDEX='ix_employee_deptid'/>
-</OPTGUIDELINES>
-*/
-```
-
-```
Rows
RETURN
( 1)
Cost
I/O
|
2.10667
FETCH
( 2)
6.84511
1
/----+----\
6.32 158
IXSCAN TABLE: SVC34
( 3) EMPLOYEE
0.0314387 Q1
0
|
158
INDEX: SVC34
IX_EMPLOYEE_DEPTID
Q1
```
+IX_EMPLOYEE_DEPTIDではなくPK_EMPLOYEE(PRIMARY KEY制約付与時に自動的に作成されたもの)を使用してもらいたい場合、以下のように最適化ガイドラインを記述します。IXSCAN要素が索引スキャンの指示であり、TABLE属性に対象の表、INDEX属性に使用する索引を指定します。
+
+```xml
+select * from employee where id < 1000 and deptid = 20
+/*
+<OPTGUIDELINES>
+ <IXSCAN TABLE='employee' INDEX='pk_employee'/>
+</OPTGUIDELINES>
+*/
+```
+
+```
+ Rows
+ RETURN
+ ( 1)
+ Cost
+ I/O
+ |
+ 2.10667
+ FETCH
+ ( 2)
+ 6.88827
+ 1
+ /----+----\
+ 52.6667 158
+ IXSCAN TABLE: SVC34
+ ( 3) EMPLOYEE
+ 0.0503776 Q1
+ 0
+ |
+ 158
+ INDEX: SVC34
+ PK_EMPLOYEE
+ Q1
+```
+
## 例2 - 結合方式の指定
次のSQLは、EMPLOYEE表からDEPARTMENT表のいずれかの部門にMANAGERIDとして登録されているレコードを取得します。
```sql
select emp.* from employee as emp inner join department as dept on emp.id = dept.managerid
```
今回RUNSTATSをかけていないためと思われますが、EXPLAINをかけるとHASH JOINが選択されてしまいました。
```
Rows
RETURN
( 1)
Cost
I/O
|
158
HSJOIN^
( 2)
7.07611
1
/-------+--------\
158 158
TBSCAN IXSCAN
( 3) ( 4)
6.93013 0.120305
1 0
| |
158 158
TABLE: SVC34 INDEX: SVC34
EMPLOYEE IX_DEPARTMENT_MANAGERID
Q2 Q1
```
これをNested Loop JOINに変更したい場合は、以下のように最適化ガイドラインを記述します。NLJOIN要素内で先に指定された表が駆動表となります。
```xml
select emp.* from employee as emp inner join department as dept on emp.id = dept.managerid
/*
<OPTGUIDELINES>
<NLJOIN>
<ACCESS TABLE='dept'/>
<ACCESS TABLE='emp'/>
</NLJOIN>
</OPTGUIDELINES>
*/
```
```
Rows
RETURN
( 1)
Cost
I/O
|
158
^NLJOIN
( 2)
16.5858
2
/---------+----------\
158 1
IXSCAN FETCH
( 3) ( 4)
0.120305 6.83796
0 1
| /----+----\
158 1 158
INDEX: SVC34 IXSCAN TABLE: SVC34
IX_DEPARTMENT_MANAGERID ( 5) EMPLOYEE
Q1 0.0272431 Q2
0
|
158
INDEX: SVC34
PK_EMPLOYEE
Q2
```
ACCESS要素で表を指定するとその表に対するアクセス方法はオプティマイザが決定しますが、前述のIXSCAN要素(索引スキャン)やTBSCAN要素(表スキャン)で表を指定すればアクセス方法を指示することも可能です。
# 参考資料
- IBM Knowledge Center
- DB2 for Linux UNIX and Windows 11.1.0
- [組み込み最適化ガイドライン](http://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0070117.html)
- [プラン最適化ガイドライン](https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0024526.html)
- [プラン最適化ガイドライン用の XML スキーマ](https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0024617.html)
[^1]: 認知度が低いためか、ミックさんのSQL実践入門にはDB2は「ヒント句を持たず、原則ユーザは実行計画を制御できない」と書かれてしまっていますが...