HiRDB SQLコーディングガイドライン

(1)

株式会社日立製作所情報・通信システム社 ITプラットフォーム事業本部 DB部

2015/06

HiRDB SQLコーディングガイドライン

(2)

3 ■ 目的データベース（以下本ドキュメントではDBと略す）へのアクセス性能は、SQL文のコーディング方法により左右されることがあります。本ガイドラインでは、DBへのアクセス性能を良くするための推奨のSQLコーディング方法を示します。本ガイドラインを各プロジェクトのSQLコーディング規約を制定する際に活用していただくことで、DBにアクセスするアプリケーションの性能トラブルを、未然に防ぐことを目的とします。なお、内容は一般的な推奨であり、必要であれば異なるSQLコーディング方法をとることを妨げるものではありません。 ■ 対象バーション本ドキュメントは、HiRDB Version 8 以降を対象としています。 ■ 留意事項本ドキュメントの例題にて、対比しているSQLは、必ずしも等価なSQLでないため、 SQL文を変形するときは、十分注意してください。 ■ コーディング例の凡例本ドキュメントの例題にて、コーディングの推奨度合を記号表記しています。記号と意味を以下に示します。 ◎：特に推奨 ○：推奨 △：改善の余地あり ×：推奨しない。改善が必要。以下のURLより、HiRDBの基礎知識を理解しておくと、本資料の理解が深まります。 http://www.hitachi.co.jp/soft/hirdb/info/tech_info.html HiRDB技術資料のURL

(5)

１-２アプリケーション開発の流れ

・・１．スキーマ設計２．プロジェクト内の SQLコーディング規約制定３．アプリケーション開発４．単体テスト５．SI 性能とのトレードオフで正規化のレベルを考える「ジョインは４表まで」など、プロジェクトで性能も含めた品質を確保するための標準を定める ← 本ガイドラインを活用コーディング規約に従って、アプリケーションを開発する SQLのアクセスパスを確認して、アプリケーション開発へフィードバック

(6)

１-３

SQLのアクセスパスの評価

5 クライアント環境定義に以下を追加し、UAP統計レポートの中で取得します。アクセスパスの取得 No. クライアント環境定義統計情報の内容１ PDCLTPATH 情報出力先。省略時は、カレントディレクトリが仮定されます。２ PDSQLTRACE トレースのファイルサイズ(byte)を指定。0を指定した場合は、ファイルの最大のサイズとなります。省略をした場合は、情報を出力しない。３ PDUAPREPLVL UAP統計レポートの出力情報を指定します。出力には、アクセスパス情報、SQL単位の情報、UAP単位の情報、SQL実行時の中間結果情報があります。アクセスパスの解析時は、aを指定し全ての情報を出力することをお勧めします。４ PDREPPATH UAP統計レポートファイルをPDCLTPATHで指定したディレクトリとは別の場所に格納したい場合に指定する。指定すると、CONNECT単位にファイルが分かれる。アクセスパスのチェック以下のキーワードで示されるアクセスパスは、定性的に負荷の高い処理であるためチェックアウトします。 ' TABLE SCAN ' …（データページ全参照[テーブルスキャン]）

' AND PLURAL INDEXES SCAN ' …（AND複数インデクス利用） ' MERGE JOIN ' …（マージジョイン）

' CROSS JOIN ' …（直積）

' FULL SCAN ' …（インデクスリーフページのフルスキャン）

' WORK TABLE SUBQ ' …（副問合せ結果の作業表への繰り返しスキャン） ' NESTED LOOPS WORK TABLE SUBQ ' …（副問合せの繰り返し実行と作業表へのスキャン）

本ドキュメントを利用しSQLコーディング後、インデクスの使用有無、結合方法などが意図した通りになっているか、効率の良いアクセスパスになっているかをSQLトレースのUAP統計レポート機能にて確認することを心がけてください。

(7)

２. インデクスを有効に使う記述

容量の多いDBを高速にアクセスする手段としてインデクスがあります。しかし、インデクスを定義したにもかかわらず、インデクスが有効に使用されないことがあります。

(8)

コーディング方法を説明する前に、まず、インデクスを

どのように使ってアクセスするデータを絞り込んでいるか、

ご説明します。

(9)

２-１-１検索方式

解説各検索方式の特徴について解説します。インデクスを有効に使う記述キースキャンインデクスを参照してインデクス中のデータ（インデクス構成列の値または行識別子）にアクセスする方式です。インデクスであまり絞り込めない場合でも、データページの入出力がなく、インデクスページを参照するだけなので、高速に検索できます。インデクススキャンインデクスを参照して条件に該当するデータを絞り込んでから、テーブルのデータをアクセスする方法です。インデクスであまり絞り込めない場合は、データページに対するランダムな入出力が増え、性能が悪くなります。特にインデクスの全範囲が検索対象になるケースをフルスキャンと呼びます。テーブルスキャン検索条件の内容にかかわらず、検索対象表の全行をシーケンシャルにアクセスする方法です。条件によって検索結果を絞り込める場合でも、すべてのデータページを参照するため、データ量が多いと性能は悪くなります。インデクスインデクス表データ _表データ

(10)

２-１-２インデクスの基本構造

9 リーフページ 412M 671M 101L ・・・ 202M 302S ・・・ 412M 591L ・・・ 591S 671L ・・・ 671M 202M 412M 論理単位説明ルートページ B-tree構造中の最上位のインデクスページ。下段のページを指しています。中間ページ B-tree構造中の中間のインデクスページ。下段のページを指しています。リーフページ B-tree構造中の最下位のインデクスページ。各インデクスページのキー値とそのアドレスを持っています。中間ページルートページ解説インデクスは、キーとキー値から構成されます。列の内容を示した列名のことをキーといいます。また、列の値のことをキー値といいます。表を検索するときの探索条件で使用する列にインデクスを作成しておくと、表の検索性能が向上します。：インデクスページ インデクスのB-tree構造 インデクスを有効に使う記述

(11)

２-１-３表検索時の条件の分類

サーチ条件：インデクスをサーチするための条件で、インデクスのサーチ範囲が決定キー条件：インデクス構成列のキー値で評価する条件で、データページ中のアクセス行を削減その他の条件：データページ中の行データを参照して、条件を評価サーチ条件＋キー条件：データページのアクセス行が決定

行の取出し

サーチ条件なしでのインデクス利用：インデクスリーフページのフルスキャンインデクスデータ解説 SQLの探索条件は、すべてサーチ条件にて評価できるのが望ましいです。そして、それは、SQLの記述により変わります。インデクスを有効に使う記述

(12)

次にインデクスを有効に使用するためのSQLコーディング

について示します。

なお、設計段階ではインデクスを定義しない予定であって

も、後のチューニングによりインデクスを定義することが考

えられるため、インデクス定義のみでインデクスが有効とな

るようなSQLにしておく必要があります。

インデクスを有効に使う記述

(13)

２-２条件指定によるインデクス効果（１）

条件指定（WHERE句）の"AND" 、"OR"指定の記述によって、指定項目が同じ場合でもインデクスの効果が低い場合があります。＝条件の項目はインデクスの構成列の先頭から連続している項目をANDで指定すると効率が良いです。例を図２-２-１、２-２-２に示します。 Point _{WHERE句のAND条件の指定順は、性能に影響しない} すべてand条件の場合  項目の順番が入れ替わる～ where C1 = 10 and C2 = 20 and C3 = 30

○

～ where C1 = 10 and C3 = 30 and C2 = 20 ～ where C3 = 30 and C2 = 20 and C1 = 10 C1、C2、C3の順番には関係なく、すべてのケースでインデクスが有効となる。 図２-２-１ "AND"、"OR"指定その１ 複数列インデクスAを C1、C2、C3の順で定義。インデクスを有効に使う記述インデクスは以下の複数列インデクス A(C1,C2,C3)

(14)

２-２-１条件指定によるインデクス効果（２）

13 すべてand条件の場合  インデクス未定義項目あり

△

～ where C1 = 10 and C4 = 40 and C3 = 30  インデクスの先頭項目なし

×

～ where C2 = 20 and C4 = 40 and C3 = 30 and条件とor条件の場合

○

～ where ( C1 = 10 ) or ( C1 = 11 and C2 = 22 )

×

～ where ( C1 = 10 and C2 = 20 ) or ( C3 = 30 ) 一致する先頭のC1のみインデクスのサーチ条件が有効となる。先頭のC1が一致しないためインデクスが無効となる（リーフページのフルスキャン）。 andが優先されるが、orのそれぞれにC1があるので、インデクスは有効になる（インデクス検索をorの両辺で２回行う）。 andが優先され、C3がorで独立していると、C3はインデクスの先頭でないのでインデクスが無効となる（リーフページのフルスキャン）。 図２-２-２ "AND"、"OR"指定その２ インデクスを有効に使う記述インデクスは以下の複数列インデクス A(C1,C2,C3)

(15)

２-２-２条件指定によるインデクス効果（例外的な事例）

インデクスを有効に使う記述 SELECT C1 ,・・・・・ FROM TBL1 WHERE C1 = ? AND C3 = ? AND CFLG = ? インデクスは以下の構成列の主キー(PK)のみ定義されていた。 TBL1(C1,C2,C3) インデクスの第２構成列C2は、条件で未使用のため、検索範囲の絞り込みは、第１構成列でのみ実施した。 PK TBL1 図２-２-３例外的な事例主キーによる検索を実施。しかし、性能が出なかった。原因は？第１構成列C1は、値がすべて同じだったため、インデクス全範囲を検索となり遅かった。インデクスを有効に使用するSQLコーディングをしていても性能が出なかった事例を示します。データの値がすべて同じ列は、基本的にインデクスから削除してください。業務等の都合で当該列をインデクスから削除が出来ない場合は、後方の構成列としてください。解説値がすべて同じ列(C1)がインデクス構成列の先頭

(16)

２-２-３探索条件と複数列インデクスのサーチ範囲

15 インデクスアアアＡ１ＡＡ２３アアアＡ４ＢＢ１２アアＢＢ３４アアＣＣ１２アアＣＣ３４イイイＡ１ＡＡ２３イイイＡ４ＢＢ１２イイＢＢ３４イイＣＣ１２イイＣＣ３４ウウウＡ１ＡＡ２３ウウウＡ４ＢＢ１２ウウＢＢ３４ウウＣＣ１２ウウＣＣ３４エエＡＡ１２エエＡＡ３４Ｃ１＝ ’イ’ ＡＮＤＣ２＝ ’Ｂ’ ＡＮＤＣ３＝２Ｃ１＝ ’イ’ ＡＮＤＣ２＝ ’Ｂ’ Ｃ１＝ ’イ’ Ｃ１＝ ’イ’ ＡＮＤＣ２＝ ’Ｂ’ ＡＮＤＣ３＞＝２Ｃ１＝ ’イ’ ＡＮＤＣ２＞＝’Ｂ’ ＡＮＤＣ３＞＝２Ｃ１＝ ’イ’ ＡＮＤＣ２＞＝’Ｂ’ ＡＮＤＣ３＝２Ｃ１＝ ’イ’ ＡＮＤＣ２＞＝’Ｂ’ Ｃ１＝ ’イ’ ＡＮＤＣ２＞＝’Ｂ’ ＡＮＤＣ３＜＝２Ｃ１＞＝ ’イ’ ＡＮＤＣ２＝ ’Ｂ’ ＡＮＤＣ３＝２Ｃ１＞＝ ’イ’ ＡＮＤＣ２＝ ’Ｂ’ ＡＮＤＣ３＞＝２Ｃ１＞＝ ’イ’ ＡＮＤＣ２＝ ’Ｂ’ Ｃ１＞＝ ’イ’ ＡＮＤＣ２＝ ’Ｂ’ ＡＮＤＣ３＜＝２Ｃ１＞＝ ’イ’ Ｃ２＝ ’Ｂ’ ＡＮＤＣ３＝２Ｃ２＝ ’Ｂ’ Ｃ３＝２Ｃ１ (C1, C2, C3) インデクスのサーチ範囲探索条件を満たす範囲上段と下段の差が大きい→ 効率悪（注）表の下3つは、リーフページのフルスキャンになり、特に効率が悪いです。上段下段Ｃ２Ｃ３インデクスを有効に使う記述

(17)

２-３条件式での列演算の使用上の注意（１）

16 スカラ演算した条件はインデクスで評価されません、またはSQL最適化オプションに「スカラ演算を含むキー条件の適用」を指定した場合は、インデクスのキー条件で評価しますが効率的に絞り込めません。例を図２-３-１、２-３-２に示します。 Point FROM句のON、WHERE句の探索条件において、列をスカラ演算しない（列を計算式の中に組み入れない）条件式に変換可能な場合、列をスカラ演算しないこと例１

○

～ WHERE TANKA > 950 – 105

×

～ WHERE TANKA + 105 > 950 例２

○

～ WHERE JDATE1 = '2002' AND JDATE2 = '0301'

○

～ WHERE (JDATE1, JDATE2) = ('2002','0301')

×

～ WHERE JDATE1 || JDATE2 = '20020301'

列を直接演算しない。図２-３-１スカラ演算の例その１列を連結して判定しない。行値構成子で記述すれば効率よくインデクスを使用できる。インデクスを有効に使う記述

(18)

２-３-１条件式での列演算の使用上の注意（２）

17

例３

○

～ WHERE SNAME LIKE 'TOKYOU%'

×

～ WHERE SUBSTR(SNAME, 1, 6) = 'TOKYOU' 例４

○

～ WHERE TANKA = 5

×

～ WHERE TANKA * 12 = 60

例５

○

～ (SNAME = 'xxx' AND ZSURYO = 1) OR (SNAME = 'yyy' AND ZSURYO <> 1)

×

～ SNAME = CASE WHEN ZSURYO = 1 THEN 'xxx' ELSE 'yyy' END 先頭文字比較はLIKE を使用する。列を直接演算しない。 CASE式を使用しない。図２-３-２スカラ演算の例その２インデクスを有効に使う記述

(19)

２-４

条件式での変数/定数へのスカラ演算の使用上の注意

変数をスカラ演算した条件はインデクスで評価されません、またはSQL最適化オプションに「スカラ演算を含むキー条件の適用」を指定した場合は、インデクスのキー条件で評価しますが効率的に絞り込めません。定数をシステム定義スカラ関数、ユーザ定義関数の引数に指定した場合は、一切インデクスが使用されません。例を図２-４-１に示します。 Point FROM句のON、WHERE句の探索条件において、 _{変数をスカラ演算しない（変数を計算式の中に組み入れない）} 定数をシステム定義スカラ関数、ユーザ定義関数の引数に指定しない変数のスカラ演算

○

～ WHERE SNAME = ?

×

～ WHERE SNAME = CAST(? AS CHAR(5))

定数のシステム定義スカラ関数

○

～ WHERE SNAME = 'ABC'

×

～ WHERE SNAME = RTRIM('ABC ')

変数をスカラ演算しない。 RTRIMは、システム定義スカラ関数。あらかじめ必要な演算を実施した定数を指定する。 ?パラメタに値を渡す埋込み変数は、あらかじめCHAR(5)のデータとしておく。図２-４-１変数/定数へのスカラ演算の例インデクスを有効に使う記述

(20)

２-５中間一致の回避

19 LIKE '%c%' でも、インデクスを使用しますが（場合によっては使用しない）、インデクスのリーフページをフルスキャンします。LIKEは、LIKE 'c%'やLIKE 'c_'のように前方一致を指定すればインデクスの参照範囲を絞り込むことができるため、処理性能が向上することが多いです。例を図２-５-１に示します。 Point LIKE述語は中間一致('%xx%')、後方一致('%xxx')を避け、極力前方一致 ('xxx%')になるように記述すること

○

～ WHERE SNAME LIKE 'TOKYOU%'

○

～ WHERE SNAME LIKE 'TOK%OU'

○

～ WHERE SNAME LIKE 'TOK___'

×

～ WHERE SNAME LIKE '%KYO%'

×

～ WHERE SNAME LIKE '%KYOU'

×

～ WHERE SNAME LIKE '___YOU'

×

～ WHERE SNAME LIKE '_O_Y_U_'

LIKE述語のパターン文字列として?パラメタや埋込み変数を指定した場合も、上記の例のように、設定するパターン文字列を前方一致になるようにすると良いです。図２-５-１ LIKEの例中間一致、後方一致は避ける。前方一致を用いる。インデクスを有効に使う記述

(21)

２-６複数列インデクスでの前方不一致検索の回避

複数列インデクスで前方不一致検索（インデクス構成列の前方の列を指定しない）を行うと、インデクスのリーフページをフルスキャンします。例を図２-６-１に示します。 Point 複数列インデクスの先頭構成列の条件を指定せず、第2構成列以降の条件を指定することは避ける

○

～ WHERE ZSURYO = 20 AND COL = 'blue'

○

～ WHERE COL = 'blue' AND ZSURYO = 20

○

～ WHERE ZSURYO = 20

×

～ WHERE COL = 'blue'

インデクス構成列の順と探索条件指定順は一致しなくて良い。図２-６-１複数列インデクス例インデクスは以下の複数列インデクス X01(ZSURYO,COL) インデクスの先頭構成列のみ指定でも良い。インデクスの後方構成列のみ指定（リーフページのフルスキャン）。インデクスを有効に使う記述

(22)

２-７インデクス列のみ選択の使用

21 問合せ指定でインデクス列のみを選択すると、インデクス列の値は表からではなく、直接インデクスから読み込まれるため、非常に高速に処理できます。（インデクスページのみの読み取りで、データページは読みません。これをキースキャンと呼びます。）例を図２-７-１に示します。 Point 選択式に指定する列は、必要最小限にし、さらにインデクス構成列のみ指定すると高速化できる

○

SELECT ZSURYO, COL FROM ZAIKO WHERE ZSURYO < 100 ; 選択式も、探索条件もインデクス構成列のみ指定すると非常に高速。図２-７-1 インデクス列のみ選択の例インデクスは以下の複数列インデクス X01(ZSURYO,COL) インデクスを有効に使う記述このことにより、探索条件に通常は指定しない列であっても、複数列インデクスに追加することによってパフォーマンスが向上する場合があります。ただし、列サイズが小さく頻繁に使用される場合のみ使用し、更新性能に十分注意してください。

(23)

２-８行値構成子の利用（１）

22 複数列の大小比較をする場合、以下のような指定を行うとインデクスの使用効率が悪くなります。行値構成子を用いれば、インデクスを効率よく利用することができます。  複数列を組み合わせての大小比較は、andとorの条件を組み合わせることにより記述できますが、インデクスの使用効率が悪くなり性能上不利になります。例を図２-８-１に示します。  連結演算を用いた大小比較が可能であった場合でも、インデクスの使用効率が悪くなり性能上不利になります。例を図２-８-２に示します。 Point _{複数列の組み合わせにて大小比較する場合は行値構成子を用いる} CODE、SUBCODEの組み合わせにて大小比較する場合

○

～ WHERE (CODE, SUBCODE) > (100, 50)

×

～ WHERE CODE > 100 OR (CODE = 100 AND SUBCODE > 50) (100, 50)よりも大きいものを検索する。行値構成子を使用すると、複数列インデクスで直接評価ができ効率が良い。図２-８-１行値構成子の使用例その１以下の複数列インデクスは定義済み X01(CODE, SUBCODE) インデクスを有効に使う記述

(24)

２-８-１行値構成子の利用（２）

23

XDATE、XTIMEの組み合わせにて大小比較する場合

○

～ WHERE (XDATE, XTIME) > ('20070301', '223000')

×

～ WHERE XDATE || XTIME > '20070301223000'

連結演算などのスカラ演算を含むとインデクスの使用効率が悪い。図２-８-２行値構成子の使用例その２以下の複数列インデクスは定義済み X02(XDATE, XTIME) ('20070301', '223000')よりも大きいものを検索する。行値構成子を使用すると、複数列インデクスで直接評価ができ効率が良い。インデクスを有効に使う記述

(25)

２-９完全一致に

LIKEは用いない

LIKE述語にて完全一致の検索を行う場合は固定長文字列でもデータ長が等しいものしか一致しない（空白を補完しない）ことも認識してください。例を図２-９-１に示します。 Point _{完全一致の検索の場合は、LIKE述語でなく＝述語を用いる}

○

～ WHERE SNAME = 'ABCD' ;

×

～ WHERE SNAME LIKE 'ABCD' ; 固定長文字列SNAMEの長さとLIKE述語に指定した文字列の長さが不一致の場合、検索できない。図２-９-１ LIKEの完全一致は用いない＝述語の場合、列長に合わせて変数の値に空白を補完するため、長さが異なっても検索できる。列定義は以下 SNAME CHAR(30) インデクスを有効に使う記述

(26)

２-１０

任意の複数条件を組み合わせた自由検索の注意

25 LIKEのパターン文字列を変数として、実行時に変数に完全一致の文字列または'%'を与えることにより一つのSQLにて任意の複数条件を組み合わせた自由検索が実現可能ですが、インデクスの使用効率が悪くなり性能劣化となるので、このような指定は避けてください。このような場合は、条件ごとにそれぞれのSQLを記述したり、動的SQLを組み立てたりして、UAPにて使用するSQLを選択してください。例を図２-１０-１、２-１０-２に示します。 Point 任意の複数条件を組み合わせた検索で、LIKE述語を使用して検索条件に汎用性を持たせないインデクスを有効に使う記述検索を有効にしたい列を事前に判定し、条件に有効にしたい列だけを指定した SQLで、検索を行う。 SNAMEの条件とSCODEの条件のアドホックな検索において： SNAMEの条件だけを有効にしたい場合

○

～ WHERE SNAME = :X1 ; SCODEの条件だけを有効にしたい場合

○

～ WHERE SCODE = :X2 ; ＝述語として不要な条件（SCODEの条件）を指定しない。なお、SQLオブジェクトキャッシュのヒット率を上げるために定数でなく変数を用いる。図２-１０-１複数のLIKE条件を組み合せて使用しないその１

(27)

２-１０-１

任意の複数条件を組み合わせた自由検索の注意

検索を有効にしたい列に定数を、有効にしない列に%を設定し、LIKEを使用した SQLを発行している。有効にしない列も検索対象となるため、インデクスの使用効率が悪くなる。

×

～ WHERE SNAME LIKE :X1 AND SCODE LIKE :X2 ;

SNAMEの条件だけを有効にしたい場合 X1 = 'ABCD' X2 = '%' SCODEの条件だけを有効にしたい場合 X1 = '%' X2 = '1234' 図２-１０-２複数のLIKE条件を組み合せて使用しないその２

(28)

２-１１

NOT(!=)のインデクスの使用上の注意

27 インデクスで範囲を絞り込めるように条件の指定ができるならば、NOT（!=）を指定しないでください。NOT（!=）を使用すると、インデクスを使用した検索を行わないことが多いです。インデクスを使用するためのNOT（!=）に関する例を図２-１１-１に示します。 Point _{インデクスを使用して範囲が絞り込めるならば、NOT（!=）は使用しない}

○

～ WHERE DNO > 0

×

～ WHERE DNO != 0 DNOに負数がない場合。 図２-１１-１インデクス定義列でのNOT(!=)の変形例 インデクスを有効に使う記述インデクスを使用して範囲をあまり絞り込めないなど、インデクスを用いたくない場合に、 NOT（!=）を用いてください。

(29)

２-１２インデクスソートキャンセル（１）

ORDER BY句の順序性は、インデクスを使用することでソート処理を行わずに保証できるため、性能向上が期待できます。また、GROUP BY句に対しても、インデクスを使用しソート処理／ハッシュ処理を行わずにグループ化できるため、性能向上が期待できます。これをインデクスソートキャンセルと呼びます。インデクスソートキャンセルにするために、以下のすべての条件を満たすようにしてください。

①ORDER BY句／GROUP BY句に指定する列のすべてが、同じ順序で一つのインデクスの第１構成列から連続しているかまたは連続しない場合には、インデクス構成列の連続しない列に、＝述語（列＝値指定）またはIS NULL条件列を探索条件に指定している。 ②ORDER BY句のASC/DESC指定と、インデクス定義時の構成列のASC/DESC指定が同じか、まったく逆。（本項目はGROUP BYには該当しない） ③インデクスが複数のRDエリアに分割されていない（HiRDB/パラレルサーバの場合は、1つのサーバ（BES内）で複数のRDエリアに分割されていない）。

Point _{ORDER BY句、GROUP BY句は、インデクスを利用できるように工夫する}

探索条件中にインデクス構成列に対する絞り込みがあるか、または選択式がインデクス構成列のみで構成されている場合は、よりインデクスソートキャンセルを適用できます。

＜補足事項＞

(30)

２-１２-１インデクスソートキャンセル（２）

29

インデクスが複数のRDエリアに分割されていない場合

○

SELECT ZSURYO, DNO FROM ZAIKO

WHERE SNAME = 'A'

ORDER BY ZSURYO DESC, DNO ASC;

○

ORDER BY SNAME ASC, ZSURYO DESC, DNO ASC;

○

ORDER BY SNAME DESC, ZSURYO ASC, DNO DESC; =述語はインデクスの第1構成列を指定。 ORDER BYはインデクスの第2構成列から連続して指定している。図２-１２-１インデクスソートキャンセルの例インデクスは以下の複数列インデクス

X01(SNAME ASC, ZSURYO DESC, DNO ASC)

選択式はインデクス構成列のみ。 ORDER BY指定列がインデクスの第1構成列から連続して指定する。 ASC/DESCがすべて逆。インデクスを有効に使う記述インデクスソートキャンセルとなる例を図２-１２-１に示します。 ORDER BY句、GROUP BY句の

(31)

２-１２-２インデクスソートキャンセル（３）

インデクスが複数のRDエリアに分割されていない場合

×

SELECT ZSURYO, DNO

FROM ZAIKO

WHERE SNAME = 'A’

ORDER BY ZSURYO ASC, DNO ASC;

×

SELECT ZSURYO, DNO

FROM ZAIKO

WHERE SNAME = 'A’

ORDER BY DNO ASC, ZSURYO ASC;

×

SELECT ZSURYO, DNO

FROM ZAIKO

WHERE SNAME = 'A’ ORDER BY DNO ASC;

×

SELECT ZSURYO, DNO

FROM ZAIKO

ORDER BY SNAME ASC, ZSURYO DESC, DNO ASC, COL ASC ;

ASC/DESCが一致しない。 ②の条件にあてはまらない例図２-１２-２インデクスソートキャンセルの不可の例その１ ORDER BY指定順序とインデクス構成列順が異なる。 ①の条件にあてはまらない例 ORDER BY指定列がインデクスの第1構成列から連続せず、欠落列の＝述語指定もない。 ①の条件にあてはまらない例 ORDER BY指定列にインデクスで定義されてない列がある。 ①の条件にあてはまらない例インデクスを有効に使う記述インデクスは以下の複数列インデクス X01(SNAME ASC, ZSURYO DESC, DNO ASC)

インデクスソートキャンセル不可となる例を図２-１２-２、２-１２-３に示します。 ORDER BY句、GROUP BY句の

(32)

２-１２-３インデクスソートキャンセル（４）

31

一つのサーバ内でインデクスが複数のRDエリアに分割されている場合

×

SELECT SURYO, DNO FROM ZAIKO

ORDER BY SNAME ASC, ZSURYO DESC, DNO ASC; インデクスが複数のRDエリアに分割されている。 ③の条件にあてはまらない例図２-１２-３インデクスソートキャンセルの不可の例その２インデクスは、以下

X01(SNAME ASC, ZSURYO DESC, DNO ASC) IN(RDA1, RDA2, RDA3);

(33)

２-１３集合関数

MAX/MINの引数の注意

ひとつのSQLに異なる列を引数とする集合関数MAX、 MINを同時に指定すると、インデクスを使用せずMAX、 MINの値を求めるため性能が悪くなります。違う引数の MAX、 MINはSQLを分け、引数にインデクスの第1構成列を指定してください。

例を図２-１３-１に示します。

Point _{一つのSQLに、異なる列を引数とするMAX、 MINを同時に指定しない}

SELECT MAX(ZA.ZSURYO) FROM ZAIKO ZA;

○

SELECT MIN(ZA.DNO) FROM ZAIKO ZA;

×

SELECT MAX(ZA.ZSURYO), MIN(ZA.DNO)

FROM ZAIKO ZA; 一つのSQLにて、MAXとMINの引数に異なる列を指定している。図２-１３-１インデクスの構成列をMAX,MINの引数に指定した例インデクスは、 X01(ZSURYO, DNO) X02(DNO) 異なる列のMAXとMINのSQL文を分けて指定する。なお、各列は第1構成列となるようにインデクスを定義する。インデクスを有効に使う記述

(34)

３. 副問合せに関する記述

33 副問合せは、SQLの構文ベースでアクセスパス（処理手順）がほぼ決まるため、副問合せを使用する場合は書き方によって大きく性能が異なります。よって、適切に使用するように心掛けてください。

(35)

３-１副問合せの使用方針

# 方針メリット 1 ジョインで記述可能なSQLは副問合せを使用せず、ジョインで記述する。検索順序、データの突合せ方法をHiRDB が最適化できるので、SQL構文をあまり考えなくて良い。 2 FROM句の副問合せは使用しない。検索順序、データの突合せ方法、絞り込みのインデクスをHiRDBが最適化できるので、 SQL構文をあまり考えなくて良い。 3 IN副問合せ（＝ANY副問合せ）とEXISTS副問合せは、絞り込みによって使い分ける。絞り込みを伝播できるようになりデータの処理量が削減できる。 4 IN副問合せ（＝ANY副問合せ）と＝副問合せは、副問合せのヒット件数が１件かどうかで使い分ける。インデクスを効率よく使用できるようになり、データの処理量が削減できる。 5 NOT IN副問合せ（<>ALL副問合せ）はなるべく使用しない。 NOTを含むとデータ処理量が多くなるが、 NOT INは特に重いので避ける。副問合せを使用する場合は、以下の順に考慮して使用すると良いです。副問合せに関する記述

(36)

３-２副問合せよりもジョインを使用する（１）

35 IN副問合せ、ANY副問合せ、ALL副問合せでは作業表を作成します。また、副問合せだと表の検索順序が限られるため、最も効率の良い順序で表を検索することができず、表の結合で記述したほうが処理の性能が向上することが多いです。IN副問合せ、=ANY 副問合せは、結合で記述できます。図３-２-１、図３-２-２に、副問合せを解消できる例を示します。 Point _{副問合せを用いないSQL文に変更可能な場合、なるべく副問合せを使用しない}

○

SELECT ZA.SNAME FROM JUTYU DE, ZAIKO ZA

WHERE DE.DNO = ZA.DNO AND ZA.ZSURYO = 20 ;

×

SELECT ZA.SNAME

FROM ZAIKO ZA WHERE DNO IN (

SELECT DE.DNO

FROM JUTYU DE) AND ZA.ZSURYO = 20 ; 副問合せを使用すると、作業表オーバヘッド、さらにインデクスが使えなければ直積相当の負荷がかかる。 ZAIKO表を先に検索して絞り込みを伝播させた方が良いが、JUTYU表を絞り込まずに先に検索してしまう。図３-２-１副問合せを使用しないSQL例その１副問合せを使用しないで、表結合で実現。（DE.DNOの値に重複がある場合は注意が必要）副問合せに関する記述

(37)

３-２-１副問合せよりもジョインを使用する（２）

○

SELECT ZA.SNAME FROM JUTYU DE, ZAIKO ZA

WHERE DE.DNO = ZA.DNO AND DE.TCODE = 'A' ;

×

SELECT ZA.SNAME

FROM ZAIKO ZA WHERE EXISTS ( SELECT *

FROM JUTYU DE

WHERE DE.DNO = ZA.DNO AND DE.TCODE = 'A') ; 外への参照あり副問合せでは、外側問合せを先に検索する。ゆえに、外側問合せにデータの絞り込みが無ければ、表の全件の突合せが実施され負荷が高い。図３-２-２副問合せを使用しないSQL例その２副問合せを使用しないで、表結合で実現。（DE.DNOの値に重複がある場合は注意が必要）副問合せに関する記述

(38)

３-３

FROM句の副問合せの使用上の注意（１）

37 FROM句の副問合せを指定すると、内部導出表を作成することが多くなります。内部導出表を作成した場合、以下のような点で性能が悪くなるため、FROM句の副問合せはなるべく用いないでください。例を図３-３-１に示します。 内部導出表は、FROM句の副問合せ結果で作成する作業表であるため、作業表へのI/Oが発生する。 内部導出表を作成すると、外側問合せに指定した探索条件は、FROM句の副問合せ結果で作成する作業表の作成後に評価するため、インデクスが用いられない。 Point _{FROM句の副問合せはなるべく用いない}

○

SELECT B.SNAME FROM JUTYU A, ZAIKO B

WHERE A.DNO = B.DNO AND A.TCODE = 'A' ;

×

SELECT B.SNAME

FROM (SELECT DNO, TCODE FROM JUTYU WHERE TCODE='A') A,

(SELECT DNO, SNAME FROM ZAIKO) B WHERE A.DNO = B.DNO ;

内部導出表を作成する。 図３-３-１ FROM句に副問合せを使用しないSQL例 DNOにインデクスを定義していても使用しない。 DNOのインデクスを用いたネストループジョインとなる。副問合せに関する記述

(39)

３-３-１

FROM句の副問合せの使用上の注意（２）

×

SELECT P.* FROM TBL2 T2, (SELECT T1.* , VALUE(T1.C1S, T1.C1L, 0) C1 FROM TBL1 T1 ) P

WHERE T2.TBL1ID = P.ID

AND P.C1 BETWEEN T2.C2 AND T2.C3 AND T2.TBL2ID = ? 図３-３-２ FROM句の副問合せの書き換えでの性能向上例 FROM句の副問合せ。

○

SELECT T1.* , VALUE(T1.C1S, T1.C1L, 0) C1 FROM TBL2 T2, TBL1 T1

WHERE T2.TBL1ID = T1.ID

AND VALUE(T1.C1S, T1.C1L, 0) BETWEEN T2.C2 AND T2.C3 AND T2.TBL2ID = ? マージジョイン作業表相手だとマージジョインのみ。 TBL2ID = ?で絞り込んだが絞り込みを伝播できず性能劣化。絞り込まれず全件検索ネストループジョイン TBL2ID = ?で絞り込んだ絞り込みを伝播。実表の検索であれば、結合条件のインデクスで絞り込んで検索。 TBL2 TBL1 TBL2 作業表 P TBL1 内部導出表 FROM句の副問合せの書き換えで性能向上した例を図３-３-２に示します。副問合せに関する記述演算は展開する

(40)

３-４

IN副問合せとEXISTSの使い分け

39 副問合せで記述する場合には、外への参照あり／なしによって、外側問合せと副問合せのどちらを先に検索するかが変わります。外への参照ありは外側問合せを先に、外への参照なしは副問合せを先に検索します。そのため、絞り込める問合せを先に検索するように、外への参照あり／なしを考える必要があります。外への参照ありで記述する場合は、EXISTS述語の副問合せを使用してください。外への参照なしで記述する場合は、IN述語の副問合せを使用してください。例を図３-４-１に示します。 Point 副問合せ内を絞り込める場合は、外への参照なしIN 副問合せを使用する副問合せより外側問合せが絞り込める場合は、外への参照ありEXISTSを使用する ○ SELECT ZA.SNAME FROM ZAIKO ZA WHERE ZA.DNO IN (

SELECT DE.DNO FROM JUTYU DE WHERE DE.TCODE = 'A') ;

○ SELECT ZA.SNAME FROM ZAIKO ZA WHERE EXISTS (

SELECT * FROM JUTYU DE WHERE DE.DNO = ZA.DNO) AND ZA.SNAME = 'A' ;

外への参照無しINの場合は、ZA.DNOにインデクスを定義して、副問合せから外側問合せへ突き合わせる。 図３-４-１外への参照なしIN と、外への参照ありEXISTSの使い分け 副問合せ内が絞り込める場合は、外への参照なしINにて、副問合せを先に検索したほうが良い。外側問合せが絞り込める場合は、外への参照ありEXISTSにて、外側問合せを先に検索したほうが良い。 DE.DNOにインデクスを定義して、外側問合せから副問合せへ突き合わせる。副問合せに関する記述

(41)

３-４-１

IN副問合せとEXISTSの使い分け（解説）

外への参照無し IN副問合せ解説外への参照有無により、先に検索する表が変わります。絞り込める表から検索できるように、外への参照有無を使い分けます。 SELECT ZA.SNAME FROM ZAIKO ZA WHERE ZA.DNO IN ( SELECT DE.DNO FROM JUTYU DE

WHERE DE.TCODE = 'A' ) ;

SELECT ZA.SNAME FROM ZAIKO ZA WHERE EXISTS ( SELECT *

FROM JUTYU DE

WHERE DE.DNO = ZA.DNO) AND ZA.SNAME = 'A' ;

ZAIKO JUTYU TCODE DNO 作業表 TCODE='A' ①副問合せ検索 ②外側問合せ検索 ZAIKO JUTYU DNO SNAME SNAME='A' ②副問合せ検索 ①外側問合せ検索外への参照あり EXISTS副問合せ副問合せに関する記述

(42)

３-５

IN副問合せと＝副問合せの使い分け

41 Point _{副問合せ内で１件しかヒットしない場合は、＝副問合せを使用する}

○

SELECT ZA.SNAME FROM ZAIKO ZA WHERE ZA.DNO = (

SELECT DE.DNO FROM JUTYU DE WHERE DE.TCODE = 'A' ) ;

×

SELECT ZA.SNAME FROM ZAIKO ZA

WHERE ZA.DNO IN (

SELECT DE.DNO FROM JUTYU DE WHERE DE.TCODE = 'A' ) ; 副問合せが１件しかヒットしないときは、 INを使用せず＝を使用する。 図３-５-１ IN副問合せと＝副問合せの使い分け 主キーであるので、１件しかヒットしない。 ZA.DNOにインデクスを定義して、副問合せから外側問合せへ突き合わせる。 JUTYU表の主キー：JUTYU(TCODE) 主キーを探索条件の＝条件で指定する場合など、副問合せ結果が１件であることが自明な場合は、IN副問合せ（=ANY副問合せも同様)を使用せず＝副問合せを使用してください。＝副問合せであれば、作業表の作成が不要なこととインデクスが自由に使用できることから、高速化できます。例を図３-５-１に示します。副問合せに関する記述

(43)

３-５-１

IN副問合せと＝副問合せの使い分け（例）

図３-５-２ IN副問合せと＝副問合せの使い分け例

○

CREATE VIEW VIEW1 AS SELECT * FROM TBL1

WHERE CCODE2 = (SELECT CCODE2 FROM TBL2

WHERE CNAME = 'NAME1') ;

×

CREATE VIEW VIEW1 AS SELECT * FROM TBL1

WHERE CCODE2 IN (SELECT CCODE2 FROM TBL2

WHERE CNAME = 'NAME1') ; SELECT T3.CID AS C1_

・・・・・・

FROM TBL3 T3

INNER JOIN VIEW1 T1

ON T3.CCODE1 = T1.CCODE1 WHERE ( T3.CID = 'A01' ・・・・・ ) ; 主キー TBL3 TBL1 ネストループジョイン TBL1(CCODE1, CCODE2) のインデクス IN副問合せではネストループジョインと同時に評価できず、探索範囲が広がる。インデクスで大量データがヒットし性能劣化ビュー表定義を＝副問合せに変更して、インデクスでの絞り込みができるようにした。副問合せに関する記述１件しかヒットしないので＝でよい主キー

(44)

３-６

NOT INの代用としてのNOT EXISTSの使用

43 表の結合では表現できないものは、副問合せで記述します。副問合せを使用するときは、なるべく外への参照を行わないようにするのが望ましいですが、外への参照を行わなくてもNOT INの場合は内部的な直積が発生するため注意が必要です。処理性能の向上のためには、NOT INを用いるよりも、外への参照をしてでもNOT EXISTSで置き換えた方が良いことが多いです。基本的にはNOT INを使用しないようにしますが、下記例でSNAME='A'が絞り込めず、かつ副問合せの検索行数が極端に少ない場合は、NOT INを用います。絞り込める場合はNOT EXISTSを使用します。ただし、このような検索は一般的に処理負荷が高いのでなるべく避けるのが望ましいです。

Point _{なるべくNOT IN副問合せよりもNOT EXISTS副問合せを用いる}

△

WHERE NOT EXISTS (

SELECT * FROM JUTYU DE WHERE DE.DNO = ZA.DNO) AND ZA.SNAME = 'A' ;

×

WHERE ZA.DNO NOT IN (

SELECT DE.DNO FROM JUTYU DE) AND ZA.SNAME = 'A' ;

副問合せ検索行数が少ない。

図３-６-１外への参照なしNOT INと、外への参照ありNOT EXISTSの使い分け

あまり絞り込めない場合。小さく絞り込める場合。

(45)

４. 結合検索に関する記述

結合検索を使用する場合、書き方によっては処理負荷が高くなります。処理負荷が高くならないようにするSQLコーディングについて示します。

(46)

４-１表の結合（１）

45 表の結合で、処理負荷がかからないように、以下に示すように指定してください。 ①結合処理は、一般的に結合表数が増えるほど処理負荷が高くなるので、結合する表数は少なくなるように工夫する。 ②表の結合は、ネストループジョインになるように設計する。あまり絞り込まないで結合を行うことが必要な場合は、ハッシュジョインになるよう設計する。極力直積、マージジョインにならないようにする。これらは、外結合（LEFT OUTER JOIN）にも該当する。 ③結合条件にはデータを絞り込めるように、＝（等号）条件を指定する。 ④結合条件にスカラ演算等の演算を使用しないようにする。スカラ演算等は直積で処理するため処理負荷が高くなる。 ⑤結合条件をOR論理演算しないようにする。OR論理演算すると直積で処理するため、処理負荷が高くなる。 ⑥絞り込める条件の列と、対する表の結合条件の列にインデクスを定義する。 ⑦HiRDB/パラレルサーバで外結合を行う場合は、表の分割列を結合条件に指定するようにする。分割列を結合条件に指定しないと、ネストループジョインにならなくなる（HiRDB/シングルサーバ、およびHiRDB/パラレルサーバでも非分割表の場合は問題ない）。 Point 表の結合は、次の点に注意してください。・不必要な表を結合しない・結合条件は＝（等号）条件を指定する・結合条件にスカラ演算等の演算を使用しない・結合条件をOR論理演算しない・ HiRDB/パラレルサーバの外結合は、表の分割列を結合条件に指定するようにする結合検索に関する記述

(47)

４-１-１表の結合（２）

結合条件をOR論理演算しないようにする例を図４-１-１に示します。図４-１-１に示す方法が適用できない場合は、集合演算（UNION ALL）を用いる方法も検討する。

○

SELECT ZA.NAME

FROM JUTYU JU, ZAIKO ZA WHERE JU.DNO = ZA.DNO AND (ZA.ZSURYO = 0

OR ZA.SNAME = 'A') ;

×

SELECT ZA.NAME

FROM JUTYU JU, ZAIKO ZA WHERE (JU.DNO = ZA.DNO AND ZA.ZSURYO = 0) OR (JU.DNO = ZA.DNO

AND ZA.SNAME = 'A') ; 結合条件をOR論理演算している。

図４-１-１直積を回避するために結合条件をORの外へ括り出す例

結合条件をOR論理演算の外へ括り出す。

(48)

４-２外結合と内結合の混在

SQLでの注意（１）

47

Point 外結合と内結合を混在する場合には内結合はINNER JOIN構文で記述する _かつ

探索条件にて最も絞り込める表をFROM句の最初に指定する

外結合と内結合の混在SQLで以下を指定した場合、データが効率的に絞り込めません。  FROMにカンマで区切り表を書き並べて結合するものと、LEFT OUTER JOINを混在して指定した場合、LEFT OUTER JOINが先に処理される。

 探索条件にて絞り込む表がLEFT OUTER JOINの外表以外であった場合、LEFT

OUTER JOINの外表を最初に検索するため、表のデータが絞り込めない状態でLEFT OUTER JOIN処理を行うので遅くなる。

上記の場合、内結合をINNER JOIN構文で記述し、探索条件にて絞り込む表をFROM句の最初に指定します。絞り込んだ表を最初に検索し、少ない行数で結合処理を行うため高速に処理できます。なお、INNER JOIN構文、LEFT OUTER JOIN構文では、FROM句の指定順に結合します。

(49)

４-２-１外結合と内結合の混在

SQLでの注意（２）

○

SELECT A.SURYO, B.SNAME, C.ZSURYO FROM JUTYU A

INNER JOIN SYOHIN B

ON A.DNO = B.DNO LEFT OUTER JOIN ZAIKO C

ON B.DNO = C.DNO WHERE A.TCODE = 'A' ;

×

SELECT A.SURYO, B.SNAME, C.ZSURYO FROM JUTYU A ,

SYOHIN B

LEFT OUTER JOIN ZAIKO C

ON B.DNO = C.DNO WHERE A.DNO = B.DNO

AND A.TCODE= 'A' ;

カンマで書き並べたJUTYU表は LEFT OUTER JOINの後に結合する。

図４-２-１外結合と内結合の混在SQL例

LEFT OUTER JOINを先に処理するためSYOHIN表を絞り込まないで最初に検索する。 INNER JOINにすることでFROM 句の記述順に結合する。よって、最初にJUTYU表をTCODE='A'にて絞り込んで検索する。外結合と内結合の混在SQLでデータを効率的に絞り込める例を図４-２-１に示します。結合検索に関する記述

(50)

４-３外結合の使用上の注意

49

LEFT OUTER JOINの内表をWHERE句で絞り込むと（NULL述語は除く）、INNER JOINと同じ結果になります。また、内表の条件をWHERE句に指定するとインデクスを使用出来ず、性能が悪くなります。ゆえに、LEFT OUTER JOINの内表はON条件中で絞り込む必要があります。

例を図４-３-１に示します。

Point _{LEFT OUTER JOINの内表はON条件中にて絞り込む}

○

SELECT A.SNAME, B.SURYO

FROM SYOHIN A LEFT OUTER JOIN JUTYU B ON A.DNO = B.DNO AND B.SURYO > 0 WHERE A.SNAME = 'A' ;

×

SELECT A.SNAME, B.SURYO

FROM SYOHIN A LEFT OUTER JOIN JUTYU B ON A.DNO = B.DNO

WHERE A.SNAME = 'A' AND B.SURYO > 0 ; SELECT A.SNAME, B.SURYO

FROM SYOHIN A INNER JOIN JUTYU B ON A.DNO = B.DNO

WHERE A.SNAME = 'A' AND B.SURYO > 0 ;

内表をWHERE句で絞り込んでいるインデクスが使用されない。

図４-３-１ LEFT OUTER JOIN 内表の絞り込み

INNER JOINと同じ結果になる。

等価なSQL

内表はON条件中にて絞り込むインデクスを使用する。

(51)

５. 表の分割に関する記述

分割表を使用することで性能向上が図れます。

(52)

© Hitachi, Ltd. 2013 , 2015. All rights reserved. 注文日注文コード商品コード数量・・_・ 2009-01-15 10111011 13425 100 ・・ 2010-03-28 12104567 92473 10 ・・ 2011-05-30 15012890 51247 50 ・・ 2012-07-06 17309684 10496 30 ・・ 2013-11-09 19109953 30929 150 ・・表の分割とは、一つの表を特定の列の値を基に複数の領域へ分割して格納し、管理する方法です。分割した表を分割表といい、表を分割するときに指定した特定の列を分割キーといいます。SQL実行時は、探索条件に分割キーを指定すると対象となるデータが格納されている領域を判定し、必要な領域にのみアクセスします。解説アプリケーション側では、表の分割／非分割を意識しなくてもアクセス可能ですが、分割キーによるアクセス範囲と（パラレルサーバの）BES間データ転送方法（５-１-１参照）を意識することで性能を向上できます。例） SELECT 商品コード, 数量 FROM 注文履歴表 WHERE 注文日 = '2011-04-01' ⇒ 検索対象のRDエリアはRDAREA3のみとなる。表の分割の詳細につきましては、マニュアル「システム導入・設計ガイド」ー「表の設計」を参照してください。表の分割に関する記述

５-１表の分割とは

51 ◆注文日による表の分割の例注文履歴表 '2010-01-01' ～'2010-12-31' RDAREA2 '2011-01-01' ～'2011-12-31' RDAREA3 '2012-01-01' ～'2012-12-31' RDAREA4 '2013-01-01'～ RDAREA5 ～'2009-12-31' RDAREA1 分割キー

(53)

５-１-１

BES間データ転送方法（パラレルサーバ）

転送方法の条件方式転送元転送先パラレルサーバで表の結合する際、BES間のデータの転送をともないます。分割表の結合では、表の分割キーを結合キーに含むことで、効率よく処理できます。解説表の分割に関する記述サーバサーバサーバサーバ分割キーによって転送先サーバを決定サーバサーバサーバサーバ常に同じ転送先のサーバにデータを転送サーバサーバサーバサーバすべての転送先サーバにデータをコピーして転送 1対1転送（ 1 TO 1 ）下記の条件をすべて満たす場合。・データ転送先の表がキーレンジ分割表またはハッシュ分割表。・転送先の表の分割キーが結合キーに含まれている。下記の条件をすべて満たす場合。・両方の表の分割キー、分割の種類、分割条件、格納先BESが完全に一致している。・両方の表の分割キーが結合キーに含まれている。分割キーが、結合キーに含まれていない。注：分割数が多く、転送元のヒット件数が多いほど、通信および結合オーバヘッドが大きくなり、性能が悪くなる。 BES間データ転送方法の種類キーレンジ転送（KEY RANGE）ハッシュ転送（HASH）ブロードキャスト転送（BROADCAST）

(54)

５-２分割した表に対する検索

RDエリアの特定

53 HiRDBは、分割列に対する探索条件が指定された場合には、探索条件に合致するデータが格納されたRDエリアを特定して他のRDエリアは検索しないようにすることで性能向上しています。しかしながら、分割列に対する探索条件がスカラ演算された場合には、該当表を格納した全RDエリアを検索するようになり性能向上できません。例を図５-２-１に示します。 Point 分割列に対する探索条件はスカラ演算しない

○

SELECT DNO FROM ZAIKO WHERE SNAME = ? ;

×

SELECT DNO FROM ZAIKO

WHERE SNAME = CAST(? AS CHAR(5)) ;

×

SELECT DNO

FROM ZAIKO

WHERE SUBSTR(SNAME,1,3) = 'ABC' ; 図５-２-１分割列に対する探索条件 ?パラメタにはCHAR(5)に変換した値を入れCAST指定を行わない。 ZAIKO表の分割列がSNAMEであった場合表の分割に関する記述

(55)

５-３結合検索時の検索

RDエリアの特定

一方の表を表の分割列で絞り込み、もう一方の表と表の分割列同士で結合した場合でも、もう一方の表は全RDエリアにアクセスします。もう一方の表も冗長に表の分割列に対する探索条件を指定することで、両表のアクセスするRDエリアを特定して絞り込むことができるため性能向上します。例を図５-３-１に示します。 Point 結合検索時には分割列に対する探索条件は省略しないで冗長に記述する

○

SELECT ZA.NAME

FROM JUTYU JU, ZAIKO ZA WHERE JU.DNO = ZA.DNO AND JU.DNO = 10 AND ZA.DNO = 10 ;

×

SELECT ZA.NAME

FROM JUTYU JU, ZAIKO ZA WHERE JU.DNO = ZA.DNO AND JU.DNO = 10 ; ZAIKO表は定義した全RDエリアを検索する可能性がある。図５-３-１分割列に対する探索条件 ZA.DNO の条件を冗長に指定することで、ZAIKO表は一つのRDエリアだけを検索する。 JUTYU表、ZAIKO表ともにDNOが分割列の場合表の分割に関する記述

(56)

５-４結合検索での表の分割列と結合条件列の関係

55 HiRDB/パラレルサーバにおいて表の結合検索を行う場合、以下に示すように指定してください。  ＝述語の結合条件に表の分割列を含むようにする。各行の結合相手のデータがどのBES＊_{に格納されているか特定できるため高速に} 処理できる。  大量データを保持する表は、分割列を揃えるだけでなく、表の分割数と格納BESを揃える。結合条件に表の分割列を含まない場合は、以下のような性能低下を招きます。  各行の結合相手のデータがどのBESに格納されているかを特定できないため一つの行に対する結合処理を結合相手の表を格納した全BESコピー転送（BROAD CASTと呼ぶ）して実行するようになるので、パラレルの効果が得られず分割損が起きる。

 LEFT OUTER JOINの場合は、外表を全行返すというSQLの仕様上、BROAD CASTが行えなくなり、両方の表の結合列での再配置を行うのでBES間のデータ転送量が増える。さらに両方の表の再配置を行うと結合列でのインデクス検索が行えないのでネストループジョイン以外の結合方式となり性能低下を招く。

例を図５-４-１に示します。

Point 結合検索時に表の分割列で結合できるように表を設計する

特に、LEFT OUTER JOINの場合は内表の分割列で結合できるように表を設計する

表の分割に関する記述

＊ DBアクセスサーバ（Back End Server）。HiRDB/パラレルサーバの構成要素の一つです。 DBへのアクセスや排他制御を実行します。

(57)

５-４-１結合検索での表の分割列と結合条件列の関係

◎

SELECT ZA.NAME

FROM JUTYU JU, ZAIKO ZA WHERE JU.DNO = ZA.DNO AND JU.CNO = 10 ;

○

SELECT ZA.NAME

FROM JUTYU JU, ZAIKO ZA WHERE JU.CNO = ZA.DNO AND JU.CNO = 10 ;

×

SELECT ZA.NAME

FROM JUTYU JU, ZAIKO ZA WHERE JU.DNO = ZA.CNO AND JU.CNO = 10 ;

×

SELECT ZA.NAME

FROM JUTYU JU

LEFT OUTER JOIN ZAIKO ZA ON JU.DNO = ZA.CNO WHERE JU.CNO = 10 ; 転送先の表の結合列に分割列を含まない。 ⇒JUTYU表データのBROAD CAST 転送が発生し、負荷が高くなる。図５-４-１分割列に対する探索条件外結合の転送先の表の結合列に分割列を含まない。 ⇒マージジョインとなり、両表のデータ転送、ZAIKO表の全件検索、作業表作成、ソート処理が発生し、負荷が高くなる。転送先の表の結合列に分割列を含む。 ⇒JUTYU表のデータをZAIKO表の分割に合わせてキーレンジ転送、またはハッシュ転送し効率が良い。分割列同士で結合する。（さらに表の分割方法が同じなら） ⇒1TO1転送となり最も処理効率が良い表の分割に関する記述 JUTYU表、ZAIKO表ともにDNOが分割列で、 ZAIKO表のDNOとCNOにインデクスが定義されている場合＜パラレル限定＞

(58)

６. DBの件数を考慮した記述

57 DBアクセス性能は、検索結果件数および検索中（SQL実行中）にアクセスするデータ件数に大きく依存します。この場合のSQLコーディングについて示します。

(59)

６-１表の件数の取得

表の件数を求めるときは、以下を指定してください。

 COUNT（＊）を使う。このとき可能な限りWITHOUT LOCK NOWAITを指定する。  条件式にはインデクスの定義された列を指定する。例を図６-１-１に示します。条件式に指定した列がインデクスの第一構成列でない場合、性能が悪くなることがあります。 Point 表の件数を求めるとき、COUNT（＊）を使用すること

○

SELECT COUNT(*) FROM ZAIKO WHERE ZSURYO > 10 WITHOUT LOCK NOWAIT ;

件数の取得には

COUNT（＊）を使用する。

ZSURYO にインデクスを定義する。

図６-１-１件数の取得のSQL例

(60)

６-２データの存在有無の取得

59

データの存在チェックを行うときの留意事項を以下に記載します。  LIMIT 1を指定して、1件見つけたら処理を打ち切るようにする。  可能な限りWITHOUT LOCK NOWAITを指定する。

 条件式と選択式にはインデクスの定義された列を指定する。  ORDER BY は作業表を作成することがあるため、指定しない。例を図６-２-１に示します。 Point データの存在チェックにはLIMITを使用すること

○

SELECT ZA.ZSURYO FROM ZAIKO ZA WHERE ZA.ZSURYO = 0 LIMIT 1

WITHOUT LOCK NOWAIT ;

ZSURYO にインデクスを定義する。

図６-２-１存在チェックのSQL例

(61)

６-３ＮＯＴ

(!=)の使用上の注意

60 NOT（!=）を使用した条件に対して、HiRDBは、あまり絞り込めないと判断し結合方法、結合順序を決定します。取り得る値が１か０だけのフラグなど２値しか持たない列は、条件に指定してもあまり絞り込めないので、NOT（!=）を使用して条件を指定することで、 HiRDBは、絞り込めないことを認識できます。３値以上持つがあまり絞り込めない場合は、 NOT INを用いても良いです。例を図６-３-１に示します。 Point 絞り込めないとわかっている条件はNOT（!=）を指定する

○

～ FROM JUTYU JU,ZAIKO ZA WHERE JU.DNO = ZA.DNO AND JU.FLAG != 1 AND ZA.ZSURYO = 20

×

～ FROM JUTYU JU,ZAIKO ZA

WHERE JU.DNO = ZA.DNO AND JU.FLAG = 0 AND ZA.ZSURYO = 20 JU.FLAGは、１か０であり、かつ ZA.ZSURYO=20が絞り込める場合 図６-３-１結合を伴うSQLでのNOT使用例 JU.FLAG != 1とすることで、 ZA.ZSURYO = 20のインデクスを確実に使用し、ZAからJUへのネストループジョインにて高速に処理する。 DBの件数を考慮した記述

(62)

６-４集合演算の使用上の注意（１）

61 UNION/UNION ALLなどの集合演算を使用したとき、集合演算で区切られた問合せ指定（SELECT文）は、別々に表アクセスします。また、集合演算を使用すると、作業表を作成することが多いです（UNION ALLのみの集合演算であれば集合演算のための作業表は作成しない）。そのため、集合演算を用いないSQL文にすると処理性能が向上することが多いです。図６-４-１に、単純な場合の例を示します。 Point 各問合せ指定の述語に指定する値だけが異なるような場合は、集合演算を使用しないようにする

○

SELECT SNAME FROM ZAIKO WHERE ZSURYO IN (10, 20) ;

×

SELECT SNAME FROM ZAIKO WHERE ZSURYO = 10 UNION SELECT SNAME FROM ZAIKO WHERE ZSURYO = 20 ; ＝条件の値だけが異なる場合は、 UNION等の集合演算を使用しない。 図６-４-１集合演算を使用しないSQL文の例 IN述語を使用することにより集合演算を使用しない。 DBの件数を考慮した記述

(63)

６-４-１集合演算の使用上の注意（２）

結合条件をOR論理演算すると、HiRDBは、直積で処理します。直積では、全データの突合せ処理を伴うため、集合演算に変形し、OR論理演算を不要とすることで直積処理が無くなり処理性能が向上することが多いです。例を図６-４-２、６-４-３に示します。 Point 結合を伴うSQLで、結合条件をOR論理演算する必要がある場合は、集合演算を使用する

○

SELECT ZA.NAME

FROM JUTYU JU, ZAIKO ZA WHERE JU.DNO = ZA.DNO UNION ALL

SELECT ZA.NAME

FROM JUTYU JU, ZAIKO ZA WHERE JU.ZNO = ZA.ZNO AND JU.DNO != ZA.DNO ;

×

SELECT ZA.NAME

FROM JUTYU JU, ZAIKO ZA WHERE (JU.DNO = ZA.DNO OR JU.ZNO = ZA.ZNO) ; DISTINCT無し。結合条件をOR論理演算している。 図６-４-２直積を回避するためにUNION ALLを利用するSQL文の例 UNION ALLを利用し、結合条件のORをなくす。 UNION ALLで処理できるように工夫する。 DBの件数を考慮した記述

(64)

６-４-２集合演算の使用上の注意（３）

63

○

SELECT ZA.NAME

FROM JUTYU JU, ZAIKO ZA WHERE JU.TCODE = 'A01' AND JU.DNO = ZA.DNO

UNION

SELECT ZA.NAME

FROM JUTYU JU, ZAIKO ZA WHERE JU.TCODE = 'A01'

AND JU.SURYO = ZA.ZSURYO ;

×

SELECT DISTINCT ZA.NAME

FROM JUTYU JU, ZAIKO ZA WHERE JU.TCODE = 'A01' AND (JU.DNO = ZA.DNO

OR JU.SURYO = ZA.ZSURYO) ; DISTINCT指定有り。結合条件をOR論理演算している。 図６-４-３直積を回避するためにUNIONを利用するSQL文の例 UNIONを利用し、結合条件のOR をなくす。 UNIONでは重複を排除するため、 DISTINCTの指定が不要。 DBの件数を考慮した記述

HiRDB SQLコーディングガイドライン