1. SVC34

    No comment

    SVC34
Changes in body
Source | HTML | Preview

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中の最適化ガイドラインは以下のようにブロックコメント/* */内に記述します。

SELECT * FROM table WHERE 
/* <OPTGUIDELINES><IXSCAN TABLE='table' INDEX="index"/></OPTGUIDELINES> */

やはりXMLでの記述が必要な点でまだ少しとっつきにくいですが、これでずいぶん導入しやすくなりました。

最適化プロファイルの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制約付与時に自動的に作成されたもの)が使用されていました。

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
 IX_EMPLOYEE_DEPTID
         Q1

PK_EMPLOYEEではなくIX_EMPLOYEE_DEPTIDを使用してもらいたい場合、以下のように最適化ガイドラインを記述します。IXSCAN要素が索引スキャンの指示であり、TABLE属性に対象の表、INDEX属性に使用する索引を指定します。

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

例2 - 結合方式の指定

次のSQLは、EMPLOYEE表からDEPARTMENT表のいずれかの部門にMANAGERIDとして登録されているレコードを取得します。

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要素内で先に指定された表が駆動表となります。

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要素(表スキャン)で表を指定すればアクセス方法を指示することも可能です。

参考資料


  1. 認知度が低いためか、ミックさんのSQL実践入門にはDB2は「ヒント句を持たず、原則ユーザは実行計画を制御できない」と書かれてしまっていますが...