4.5.1　SQL最適化モード

〈この項の構成〉

(1)　SQL最適化モードの特徴
(2)　強制的にコストベース最適化モード2を適用するSQL
(3)　SQL最適化オプション，SQL拡張最適化オプションの有効範囲
(4)　最適化処理で選択されたSQL最適化モードを確認する方法
(5)　注意事項

(1)　SQL最適化モードの特徴

SQL最適化モードの特徴を次の表に示します。

表4‒5　SQL最適化モードの特徴
SQL最適化モード	説明	長所	短所	選択方法
コストベース最適化モード1	バージョン06-00より前のHiRDBのコストベース最適化処理方式です。バージョン06-00以降のHiRDBでも使用できます。	バージョン06-00より前のHiRDBからバージョンアップしても，バージョンアップ前と同じアクセスパスで検索できます。なお，高速に検索する目的として，アクセスパスを変更することもあります。	候補とするアクセスパスの種類が少ないため（ハッシュジョインなどの機能を候補として選択しません），必ずしも最適なアクセスパスが選択されるとは限りません。	SQL拡張最適化オプションに"NONE"又は0を指定してください。なお，SQL文によっては，常にコストベース最適化モード2を使用する場合があります。詳細については，「強制的にコストベース最適化モード2を適用するSQL」を参照してください。
コストベース最適化モード2	バージョン06-00以降のHiRDBで，高速に検索できるようにしたコストベース最適化処理方式です。	結合検索，副問合せ処理に対して，ハッシングを組み合わせたアクセスパスを候補として選択するため，高速に検索できます。	複雑な最適化処理をするため，最適化処理に時間が掛かります。	SQL拡張最適化オプションにコストベース最適化モード2の適用を指定するか，又はSQL拡張最適化オプションを省略してください。

ページの先頭へ

(2)　強制的にコストベース最適化モード2を適用するSQL

コストベース最適化モード1を使用していても，コストベース最適化モード2が強制的に適用される場合があります。強制的にコストベース最適化モード2が適用されるSQLを次に示します。

UPDATE文のSET句での副問合せ
OUTER JOIN，INNER JOIN，又はCROSS JOIN
集合演算結果のCOUNT(*)
DISTINCT集合関数の値式
ビュー表，WITH句の問合せ名の外結合（OUTER JOIN）への指定
BLOBデータ，BINARYデータの部分的な更新・検索
SQL最適化指定
定義長が255バイトを超える値式のソート
先頭からn行の検索結果を取得する機能
BINARY型を使用した検索
内部導出表が2段以上入れ子になるビュー表，WITH句の検索
マトリクス分割
結合表に対する副問合せ
繰返し列での集合関数MIN，MAX適用
行値構成子
CASE式中の副問合せ
値式2がBLOB型のPOSITION関数
参照制約
検査制約
定義長256バイト以上のデータの制限解除
更新，削除，又は追加をする表の副問合せへの指定
FROM句での繰返し列の平坦化機能
LIMIT句
内部導出表が2段以上入れ子になる検索
問合せ式本体の指定箇所拡大
ウィンドウ関数
SIMILAR述語
XML型を使用した検索
文字集合
RDエリア名を指定した検索，更新，又は削除
圧縮列へのアクセスを含む操作系SQL
一時表に対して操作をするSQL
列追加定義（ALTER TABLE文）でON ROW EXISTS指定の列を追加した表の検索

それぞれのSQLに該当する適用条件及び例を次に示します。

(a)　UPDATE文のSET句での副問合せ

UPDATE文のSET句に，スカラ副問合せ又は行副問合せを指定した場合

例：

UPDATE T1 SET(C1,C2)=(SELECT MAX(C1),MAX(C2) FROM T2) WHERE C3=1
 
注　下線部分が該当箇所です。

(b)　OUTER JOIN，INNER JOIN，又はCROSS JOIN

FROM句に，[INNER] JOINを指定した場合

例：

SELECT T1.C1,T2.C2 FROM T1 INNER JOIN T2 ON T1.C1=T2.C1
 
注　下線部分が該当箇所です。

FROM句に，LEFT [OUTER] JOINを含む表参照と任意の表参照をコンマで区切り複数指定した場合

例：

SELECT T1.C1,T2.C2 FROM T1 LEFT OUTER JOIN T2 ON T1.C1=T2.C1,
  T3 WHERE T1.C1=T3.C1
 
注　下線部分が該当箇所です。

FROM句に，表参照1 LEFT [OUTER] JOIN 表参照2を指定し，更に表参照2にLEFT [OUTER] JOINをネストして指定した場合

例：

SELECT T1.C1,T2.C2,T3.C2 FROM T1 LEFT OUTER JOIN
  (T2 LEFT OUTER JOIN T3 ON T2.C1=T3.C1)
  ON T1.C1=T3.C1
 
注　下線部分が該当箇所です。

FROM句にRIGHT [OUTER] JOINを指定した場合

例：

SELECT T1.C1,T2.C2 FROM T1 RIGHT OUTER JOIN T2 ON T1.C1=T2.C1
 
注　下線部分が該当箇所です。

FROM句にCROSS JOINを指定した場合

例：

SELECT T1.C1,T2.C2 FROM T1 CROSS JOIN T2
 
注　下線部分が該当箇所です。

(c)　集合演算結果のCOUNT(*)

FROM句に指定した問合せ式本体が集合演算を含む場合

例：

SELECT COUNT(*) FROM (SELECT C1 FROM T1 UNION SELECT C1 FROM T2)
 
注　下線部分が該当箇所です。

(d)　DISTINCT集合関数の値式

DISTINCT集合関数（COUNT，SUM，又はAVG）の引数に，列指定を除く値式を指定した場合

例：
```
SELECT AVG(DISTINCT C1+C2) FROM T1
 
注　下線部分が該当箇所です。
```

(e)　ビュー表，WITH句の問合せ名の外結合（OUTER JOIN）への指定

FROM句にビュー表又はWITH句の問合せ名に対するLEFT [OUTER] JOINを指定し，そのビュー表又はWITH句の問合せ名から内部導出表が作成される場合

例：
```
WITH W1(C1,C2) AS (SELECT C1,COUNT(*) FROM T1 GROUP BY C1)
  SELECT W1.C1,W1.C2,T2.C2 FROM W1 LEFT JOIN T2 ON W1.C1=T2.C1
 
注　下線部分が該当箇所です。
```

(f)　BLOBデータ，BINARYデータの部分的な更新・検索

スカラ関数SUBSTRの値式1に，BLOB型を指定した場合

例：

SELECT SUBSTR(C1,1,500) FROM T1
 
注　下線部分が該当箇所です。C1がBLOB型の列です。

UPDATE文の更新対象がBLOB型の列で，更新値に連結演算を指定した場合

例：

UPDATE T1 SET C1=C1||?
 
注　下線部分が該当箇所です。C1がBLOB型の列です。

UPDATE文の更新対象がBLOB型の列又はBLOB属性で，更新値に列指定又はコンポネント指定を指定した場合

例：
```
UPDATE T1 SET C1=C2
 
注　下線部分が該当箇所です。C1，C2がBLOB型の列です。
```

(g)　SQL最適化指定

使用インデクスのSQL最適化指定を指定した場合

例：

SELECT T1.C1 FROM T1 WITH INDEX(idx1) WHERE T1.C2<=500
 
注　下線部分が該当箇所です。

結合方式のSQL最適化指定を指定した場合

例：

SELECT T1.C1,T2.C2 FROM T1 INNER JOIN BY NEST T2 ON T1.C1=T2.C1
 
注　下線部分が該当箇所です。

副問合せ実行方式のSQL最適化指定を指定した場合

例：

SELECT T1.C1 FROM T1 WHERE T1.C1=ANY
  (HASH SELECT T2.C1 FROM T2 WHERE T2.C2='302S')
 
注　下線部分が該当箇所です。

(h)　定義長が255バイトを超える値式のソート

ORDER BY句のソートのキーになる項目に，定義長が256バイト以上のCHAR，VARCHAR，MCHAR，及びMVARCHAR，並びに128文字以上のNCHAR及びNVARCHARを指定した場合

例１：

SELECT C1,C2 FROM T1 ORDER BY C2
 
注　下線部分が該当箇所です。C2はVARCHAR(300)の列です。

例２：

SELECT C1,C3||C4 FROM T1 ORDER BY 2
 
注　下線部分が該当箇所です。C3||C4はNCHAR(150)の値式です。

(i)　先頭からn行の検索結果を取得する機能

ORDER BY句の直後にLIMIT句を指定する場合

例：

SELECT SCODE,ZSURYO FROM ZAIKO WHERE　ZSURYO>20 ORDER BY 2,1 LIMIT 10
 
注　下線部分が該当箇所です。

(j)　BINARY型を使用した検索

BINARY型の列を検索する場合

例：

SELECT C1 FROM T1
 
注　下線部分が該当箇所です。C1はBINARY型の列です。

(k)　内部導出表が2段以上入れ子になるビュー表，WITH句の検索

FROM句にビュー表又はWITH句の問合せ名を指定した問合せ指定があり，更にこのビュー表定義中又はWITH句中の導出問合せ式のFROM句に，内部導出表となるビュー表又はWITH句を指定している場合

例：
```
WITH Q1(QC1,QC2) AS (SELECT C1,C2 FROM V1 GROUP BY C1,C2)
 
SELECT AVG(QC1),QC2 FROM Q1 GROUP BY QC2
 
注　下線部分が該当箇所です。V1は内部導出表となるビュー表です。
```

(l)　マトリクス分割

マトリクス分割表に対して，検索，更新，削除，及びリストの操作をする場合

例：
```
SELECT * FROM T1
 
注　下線部分が該当箇所です。T1はマトリクス分割表です。
```

(m)　結合表に対する副問合せ

結合表を含む問合せ指定を指定し，FROM句のON 探索条件，WHERE句，又はHAVING句に副問合せを指定する場合

例：
```
SELECT * FROM T1 LEFT JOIN T2 ON T1.C1=T2.C1
  WHERE T1.C1=ANY(SELECT C1 FROM T3)
 
注　下線部分が該当箇所です。
```

(n)　繰返し列での集合関数MIN，MAX適用

集合関数のMIN又はMAXに，FLAT指定の繰返し列を指定する場合

例：

SELECT MIN(FLAT(C1)) FROM T1
 
注　下線部分が該当箇所です。C1は繰返し列です。

(o)　行値構成子

行値構成子を指定する場合

例：

SELECT * FROM T1 WHERE (C1,C2,C3)>(1,2,3)
 
注　下線部分が該当箇所です。

(p)　CASE式中の副問合せ

CASE式中に副問合せを指定する場合

例：

SELECT CASE(SELECT C1 FROM T1) WHEN 1 THEN C2 ELSE C1 END FROM T1
 
注　下線部分が該当箇所です。

(q)　値式2がBLOB型のスカラ関数POSITION

スカラ関数POSITIONの値式2にBLOB型を指定する場合

例：

SELECT POSITION(? AS BLOB(1K) IN C1) FROM T1
 
注　下線部分が該当箇所です。C1はBLOB型の列です。

(r)　参照制約

被参照表又は参照表に対して，挿入，更新，及び削除をする場合

例：

UPDATE T1 SET C1=?
 
注　下線部分が該当箇所です。T1は被参照表又は参照表です。

(s)　検査制約

検査制約を定義した列に対して，挿入，及び更新をする場合

例：

INSERT INTO T1(C1,C2) VALUES(?,?)
 
注　下線部分が該当箇所です。C1は検査制約を定義した列です。

(t)　定義長256バイト以上のデータの制限解除

GROUP BY句に次の値式を指定する場合
- 定義長が256バイト以上のCHAR，VARCHAR，MCHAR，又はMVARCHAR型
- 128文字以上のNCHAR，又はNVARCHAR型
- 256バイト以上のBINARY型
例：
```
SELECT C1,COUNT(*) FROM T1 GROUP BY C1
 
注　下線部分が該当箇所です。T1.C1が256バイト以上の文字列です。
```
集合関数の引数に次の値式を指定する場合
- 定義長が256バイト以上のCHAR，VARCHAR，MCHAR，又はMVARCHAR型
- 128文字以上のNCHAR，又はNVARCHAR型
- 256バイト以上のBINARY型
例：
```
SELECT MIN(C1) FROM T1
 
注　下線部分が該当箇所です。T1.C1が256バイト以上の文字列です。
```
ビュー表，WITH句，又はFROM句に問合せ式本体を指定し，内部導出表を作成する場合で，内部導出表の選択式に次の値式を指定するとき
- 256バイト以上のCHAR，VARCHAR，MCHAR，又はMVARCHAR型
- 128文字以上のNCHAR，又はNVARCHAR型
- 256バイト以上のBINARY型
例：
```
WITH W1(C1,C2) AS (SELECT DISTINCT C1,C2 FROM T1)
    SELECT C2,COUNT(*) FROM W1 GROUP BY C2
 
注　下線部分が該当箇所です。T1.C1が256バイト以上の文字列です。
```

(u)　更新，削除，又は追加をする表の副問合せへの指定

更新，削除，又は追加をする表を副問合せに指定する場合

例1：
 
UPDATE T1 SET C1=NULL WHERE C1=(SELECT MIN(C1) FROM T1)
 
例2：
 
DELETE FROM T1 WHERE C1=(SELECT MIN(C1) FROM T1)
 
例3：
 
INSERT INTO T1(C1,C2) VALUES((SELECT MIN(C1) FROM T1),NULL)
 
注　下線部分が該当箇所です。

INSERT文の問合せ式本体に，追加する表と同一表を指定する場合

例：

INSERT INTO T1(C1,C2) SELECT C1,C2+1 FROM T1
 
注　下線部分が該当箇所です。

(v)　FROM句での繰返し列の平坦化機能

FROM句にFLATを指定する場合

例：

SELECT C1,C2 FROM T1(FLAT(C1,C2)) WHERE C1<10 AND C2 >20
 
注　下線部分が該当箇所です。C1，C2は繰返し列です。

(w)　LIMIT句

LIMIT句を指定する場合

例：

SELECT SCODE, ZSURYO FROM ZAIKO WHERE ZSURYO > 20ORDER BY 2, 1 LIMIT 20, 10
 
注　下線部分が該当箇所です。

(x)　内部導出表が2段以上入れ子になる検索

内部導出表を作成する問合せ指定のFROM句に，更に内部導出表となる問合せ指定を指定している場合

例：

SELECT AVG(QC1),QC2 FROM(SELECT C1,C2 FROM V1 GROUP BY C1,C2) AS Q1(QC1,QC2)
 
注　下線部分が該当箇所です。V1は内部導出表となるビュー表です。

(y)　問合せ式本体の指定箇所拡大

ビュー表，WITH句，又はFROM句に集合演算を指定し，この問合せが内部導出表を作成する場合

例：

WITH V1(C1,C2) AS (SELECT C1,C2 FROM T1 UNION SELECT C1,C2 FROM T2)
    SELECT C1 FROM V1 WHERE C2>0
 
注　下線部分が該当箇所です。

INSERT文に集合演算を指定する場合

例：

INSERT INTO T3 (C1,C2)
    SELECT C1,C2 FROM T1 UNION ALL SELECT C1,C2 FROM T2
 
注　下線部分が該当箇所です。

副問合せに集合演算を指定する場合

例：

SELECT C1, C2 FROM T3
    WHERE EXISTS(SELECT C1 FROM T1 EXCEPT SELECT C1 FROM T2)
 
注　下線部分が該当箇所です。

(z)　ウィンドウ関数

選択式の中にウィンドウ関数を含む場合

例：

SELECT C1,C2,COUNT(*) OVER() FROM T1
 
注　下線部分が該当箇所です。

(aa)　SIMILAR述語

SIMILAR述語を指定する場合

例：

SELECT C1 FROM T1 WHERE C2 SIMILAR TO '%(b|g)%'
 
注　下線部分が該当箇所です。

(ab)　XML型を使用した検索

XML型を使用した検索を行った場合

例：

SELECT C1 FROM T1
    WHERE XMLEXISTS('/書籍情報[価格=1000]'
                     PASSING BY VALUE C2)
 
注　下線部分が該当箇所です。T1.C2がXML型の列です。

(ac)　文字集合

SQL中に文字集合を指定した列を含む場合

例：

SELECT C1, C2 FROM T1 WHERE C1='HiRDB'
 
注　下線部分が該当箇所です。T1.C1が文字集合を指定した列です。

(ad)　RDエリア名を指定した検索，更新，又は削除

SQL中にアクセス先のRDエリア名を指定した場合

例：

SELECT C1 FROM T1 IN (‘RU01,RU02’) WHERE C1='HiRDB'
 
注　下線部分が該当箇所です。RU01，RU02がアクセス先のRDエリアです。

(ae)　圧縮列へのアクセスを含む操作系SQL

SQL中に圧縮列を含む場合

例：

SELECT C1,C2 FROM T1 WHERE C2<?
 
注　下線部分が該当箇所です。T1.C1が圧縮列です。

(af)　一時表に対して操作をするSQL

一時表に対して操作する場合

例：

SELECT C1 FROM TTMP1
 
注　下線部分が該当箇所です。TTMP1が一時表です。

(ag)　列追加定義（ALTER TABLE文）でON ROW EXISTS指定の列を追加した表を含む操作系SQL

操作系SQL中に列追加定義（ALTER TABLE文）でON ROW EXISTS指定の列を追加した表を含む場合

例：

CREATE TABLE T1(C1 INT)
ALTER TABLE T1 ADD C2 INT DEFAULT 2 ON ROW EXISTS
SELECT C1 FROM T1
 
注　下線部分が該当箇所です。T1が列追加定義(ALTER TABLE文)でON ROW EXISTS指定の列を追加した表です。

ページの先頭へ

(3)　SQL最適化オプション，SQL拡張最適化オプションの有効範囲

SQL最適化オプション，SQL拡張最適化オプションが有効となるSQL最適化モードを次の表に示します。

表4‒6　SQL最適化オプション，SQL拡張最適化オプションが有効となるSQL最適化モード
SQL最適化モード	SQL最適化オプション	SQL拡張最適化オプション
コストベース最適化モード1	○	×
コストベース最適化モード2	○	○

（凡例）

○：有効となります。

×：無効となります。

ページの先頭へ

(4)　最適化処理で選択されたSQL最適化モードを確認する方法

アクセスパス表示ユティリティを使用すると，SQL文ごとに最適化処理で選択されたSQL最適化モードを確認できます。アクセスパス表示ユティリティについては，マニュアル「HiRDB コマンドリファレンス」を参照してください。

ページの先頭へ

(5)　注意事項

SQL最適化モードを変更すると，アクセスパスが変更になるため，SQL文の検索性能が低下することがあります。本番運用などで十分に性能評価ができない環境では，SQL最適化モードを変更しないことをお勧めします。
HiRDBを新規導入する場合，コストベース最適化モード2を使用することをお勧めします。また，ほかのSQL拡張最適化オプションを使用する場合は，コストベース最適化モード2に追加する形で使用してください。コストベース最適化モード2を使用すると，最適化処理で選択できるアクセスパスの種類が多いため，より高速に検索できるアクセスパスを選択できます。

なお，HiRDBシステム定義のpd_additional_optimize_levelオペランドの省略値はコストベース最適化モード2が含まれるため，通常はコストベース最適化モード2が適用されます。
バージョン06-00より前のHiRDBからバージョンアップする場合，バージョンアップ前と同じ状態で使用するためにコストベース最適化モード1をそのまま使用することをお勧めします。ただし，SQL文によっては，常にコストベース最適化モード2を使用することがあります。
通常は絞り込み条件を考慮して最適化をしますが，SQL拡張最適化オプションにハッシュジョイン，副問合せのハッシュ実行を適用した場合，絞り込み条件がなかったり，絞り込み条件で行数があまり絞り込めなかったりすると，行数の多い表を内表にしたハッシュジョインを適用したり，行数の多い表の転送が発生したりします。このような場合は，表の行数の情報を最適化に反映させるために，必要に応じて次のどちらかの方法で最適化情報収集ユティリティを実行してください。最適化情報収集ユティリティの実行要否については，マニュアル「HiRDB コマンドリファレンス」を参照し，性能について十分に検証するようにしてください。
- 表にデータを格納した状態で，最適化情報収集レベルをlvl1にして（-cオプションにlvl1を指定して）実行します。lvl1では，表の行数の情報だけを取得するため，比較的短時間で最適化情報収集ユティリティを実行できます。また，-tオプションにALLを指定すると，スキーマ内のすべての表に対して行数を取得できます。
- 表にデータを格納できない場合や，テスト環境の場合は，本番環境での表の行数（NROWS）を最適化パラメタファイルに記述して，表ごとに-sオプションを指定して実行します。表の行数を1,000行にする場合の最適化パラメタファイルの記述例を次に示します。
  
  # 表最適化情報
  
  NROWS 1000 # 表の全行数
コストベース最適化モード1を使用する場合，通常は最適化情報収集ユティリティを実行する必要はありませんが，実行するときは最適化情報収集レベルはlvl1にしないでください。

ページの先頭へ

4.5.1 SQL最適化モード

(1) SQL最適化モードの特徴

(2) 強制的にコストベース最適化モード2を適用するSQL

(a) UPDATE文のSET句での副問合せ

(b) OUTER JOIN，INNER JOIN，又はCROSS JOIN

(c) 集合演算結果のCOUNT(*)

(d) DISTINCT集合関数の値式

(e) ビュー表，WITH句の問合せ名の外結合（OUTER JOIN）への指定

(f) BLOBデータ，BINARYデータの部分的な更新・検索

(g) SQL最適化指定

(h) 定義長が255バイトを超える値式のソート

(i) 先頭からn行の検索結果を取得する機能

(j) BINARY型を使用した検索

(k) 内部導出表が2段以上入れ子になるビュー表，WITH句の検索

(l) マトリクス分割

(m) 結合表に対する副問合せ

(n) 繰返し列での集合関数MIN，MAX適用

(o) 行値構成子

(p) CASE式中の副問合せ

(q) 値式2がBLOB型のスカラ関数POSITION

(r) 参照制約

(s) 検査制約

(t) 定義長256バイト以上のデータの制限解除

(u) 更新，削除，又は追加をする表の副問合せへの指定

(v) FROM句での繰返し列の平坦化機能

(w) LIMIT句

(x) 内部導出表が2段以上入れ子になる検索

(y) 問合せ式本体の指定箇所拡大

(z) ウィンドウ関数

(aa) SIMILAR述語

(ab) XML型を使用した検索

(ac) 文字集合

(ad) RDエリア名を指定した検索，更新，又は削除

(ae) 圧縮列へのアクセスを含む操作系SQL

(af) 一時表に対して操作をするSQL

(ag) 列追加定義（ALTER TABLE文）でON ROW EXISTS指定の列を追加した表を含む操作系SQL

(3) SQL最適化オプション，SQL拡張最適化オプションの有効範囲

(4) 最適化処理で選択されたSQL最適化モードを確認する方法

(5) 注意事項

4.5.1　SQL最適化モード

(1)　SQL最適化モードの特徴

(2)　強制的にコストベース最適化モード2を適用するSQL

(a)　UPDATE文のSET句での副問合せ

(b)　OUTER JOIN，INNER JOIN，又はCROSS JOIN

(c)　集合演算結果のCOUNT(*)

(d)　DISTINCT集合関数の値式

(e)　ビュー表，WITH句の問合せ名の外結合（OUTER JOIN）への指定

(f)　BLOBデータ，BINARYデータの部分的な更新・検索

(g)　SQL最適化指定

(h)　定義長が255バイトを超える値式のソート

(i)　先頭からn行の検索結果を取得する機能

(j)　BINARY型を使用した検索

(k)　内部導出表が2段以上入れ子になるビュー表，WITH句の検索

(l)　マトリクス分割

(m)　結合表に対する副問合せ

(n)　繰返し列での集合関数MIN，MAX適用

(o)　行値構成子

(p)　CASE式中の副問合せ

(q)　値式2がBLOB型のスカラ関数POSITION

(r)　参照制約

(s)　検査制約

(t)　定義長256バイト以上のデータの制限解除

(u)　更新，削除，又は追加をする表の副問合せへの指定

(v)　FROM句での繰返し列の平坦化機能

(w)　LIMIT句

(x)　内部導出表が2段以上入れ子になる検索

(y)　問合せ式本体の指定箇所拡大

(z)　ウィンドウ関数

(aa)　SIMILAR述語

(ab)　XML型を使用した検索

(ac)　文字集合

(ad)　RDエリア名を指定した検索，更新，又は削除

(ae)　圧縮列へのアクセスを含む操作系SQL

(af)　一時表に対して操作をするSQL

(ag)　列追加定義（ALTER TABLE文）でON ROW EXISTS指定の列を追加した表を含む操作系SQL

(3)　SQL最適化オプション，SQL拡張最適化オプションの有効範囲

(4)　最適化処理で選択されたSQL最適化モードを確認する方法

(5)　注意事項