はじめに
Verticaでは、テーブルを結合する際、自動的に結合されるテーブルの順番を判断し、実行されます。しかし、結合の際、最良なテーブルの順番が判断されずに、性能劣化が発生してしまうことがあります。
本記事では、その際に有効な対処である、テーブルの結合順番を強制する方法について紹介します。
結合(Hash Join)による動作
結合が行われる際、Hash Joinが選択された場合の基本的な動作は以下です。1.結合する表の件数を比較し、件数の少ない方の表(以下table1)を外部テーブルとして全件読み取る
2.外部テーブルの結合条件キー列の値をハッシュ関数にかけてHash tableを作成する
3.内部テーブル(以下table2)の結合キー列を同じハッシュ関数で変換し、作成したHash tableを検索する
4.ハッシュが同じ列同士を結合し、結果を表示する
2.外部テーブルの結合条件キー列の値をハッシュ関数にかけてHash tableを作成する
3.内部テーブル(以下table2)の結合キー列を同じハッシュ関数で変換し、作成したHash tableを検索する
4.ハッシュが同じ列同士を結合し、結果を表示する
[イメージ図]
上記のように結合されるテーブルの順番(外部テーブル、内部テーブル)を自動で判断し、実行されます。
外部テーブル、内部テーブルの概要、判断基準は以下です。
外部テーブル：最初にアクセスされる表です。駆動表とも呼ばれます。基本は、小さいテーブルが指定されます。
内部テーブル：外部テーブルから参照される表です。基本は、大きいテーブルが指定されます。
内部テーブル：外部テーブルから参照される表です。基本は、大きいテーブルが指定されます。
性能劣化の原因と対処方法
原因
結合を行う際、件数が少ない方の表を外部テーブルとして読み込むことが理想ですが、
以下図のように、件数が多い表を外部テーブルとして読み込んでしまう場合があり、
その際は処理に大幅な時間が必要となる場合がございます。
[イメージ図]
対処方法
以下の手順にて各テーブルに数値を設定して重みづけをすることで、結合の順番を制御できます。Step1：「EnableForceOuter」の有効化
Step2：テーブル毎に「FORCE OUTER」の値を指定
Step3：実行計画の確認
Step2：テーブル毎に「FORCE OUTER」の値を指定
Step3：実行計画の確認
Step1：「EnableForceOuter」の有効化
データベースパラメーター「EnableForceOuter」を有効化することで、結合時、内部テーブル/外部テーブルの制御が可能になります。
|
1 |
SQL=> ALTER DATABASE <DB名> SET EnableForceOuter= 1 ; |
Step2：テーブル毎に「FORCE OUTER」の値を指定
FORCE OUTERは結合時、どちらを内部テーブル、外部テーブルとするかの基準となる相対値です。
こちらの値をテーブル毎に指定します。
–FORCE OUTERの値が相対的に小さいテーブルは、結合時に内部テーブルとして指定されます。
–FORCE OUTERの値が相対的に大きいテーブルは、結合時に外部テーブルとして指定されます。
以下の例では、table1を外部テーブルとするために[8]、table2を内部テーブルとするため[3]を設定をします。
|
1 2 |
SQL=> ALTER TABLE table1 FORCE OUTER 8; SQL=> ALTER TABLE table2 FORCE OUTER 3; |
Step3：実行計画の確認
さいごに、実行計画から想定通りの外部テーブル、内部テーブルが指定されている事を確認します。
実行計画の確認方法はこちらをご参照ください。
[変更前]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
SQL=> EXPLAIN select ename,sal,dname from table2 join table1 on table2.deptno = table1.deptno where sal >=3000; QUERY PLAN ------------------------- ------------------------------ QUERY PLAN DESCRIPTION: ------------------------------ EXPLAIN select ename,sal,dname from table2 join table1 on table2.deptno = table1.deptno where sal >=3000; Access Path: +-JOIN HASH [Cost: 1K, Rows: 10K (NO STATISTICS)] (PATH ID: 1) Outer (RESEGMENT )(LOCAL ROUND ROBIN) Inner (RESEGMENT) | Join Cond: (table2.DEPTNO = table1.DEPTNO) | Execute on: All Nodes #table2が外部テーブルとして選択されています# | +-- Outer -> STORAGE ACCESS for table2 [Cost: 826, Rows: 10K (NO STATISTICS)] (PATH ID: 2) | | Projection: public.table2_b0 | | Materialize: table2.DEPTNO, table2.ENAME, table2.SAL | | Filter: (table2.SAL >= 3000) | | Filter: (table2.DEPTNO IS NOT NULL) | | Execute on: All Nodes | +-- Inner -> STORAGE ACCESS for table1 [Cost: 404, Rows: 10K (NO STATISTICS)] (PATH ID: 3) | | Projection: public.table1_b0 | | Materialize: table1.DEPTNO, table1.DNAME | | Execute on: All Nodes |
[変更後]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
SQL=> EXPLAIN select ename,sal,dname from table1 join table2 on table1.deptno = table2.deptno where sal >=3000; QUERY PLAN --------------------------------- ------------------------------ QUERY PLAN DESCRIPTION: ------------------------------ EXPLAIN select ename,sal,dname from table1 join table2 on table1.deptno = table2.deptno where sal >=3000; EnableForceOuter is on #EnableForceOuterが有効な場合に表示されます# Access Path: +-JOIN HASH [Cost: 1K, Rows: 10K (NO STATISTICS)] (PATH ID: 1) Outer (RESEGMENT )(LOCAL ROUND ROBIN) Inner (RESEGMENT) | Join Cond: (table1.DEPTNO = table2.DEPTNO) | Execute on: All Nodes #table1が外部テーブルとして選択されています# | +-- Outer -> STORAGE ACCESS for table1 [Cost: 404, Rows: 10K (NO STATISTICS)] (PATH ID: 2) | | Projection: public.table1_b0 | | Force outer level: 8 | | Materialize: table1.DEPTNO, table1.DNAME | | Execute on: All Nodes | +-- Inner -> STORAGE ACCESS for table2 [Cost: 826, Rows: 10K (NO STATISTICS)] (PATH ID: 3) | | Projection: public.table2_b0 | | Force outer level: 3 | | Materialize: table2.DEPTNO, table2.ENAME, table2.SAL | | Filter: (table2.SAL >= 3000) | | Filter: (table2.DEPTNO IS NOT NULL) | | Execute on: All Nodes |
検証バージョンについて
この記事の内容はVertica 12.0で確認しています。参考情報
Controlling Join Inputshttps://www.vertica.com/docs/12.0.x/HTML/Content/Authoring/AnalyzingData/Queries/Joins/ForceInnerOuterJoinInputs.htm
「Join inner did not fit in memory」エラーの原因と対処方法
http://vertica-tech.ashisuto.co.jp/join_inner_did_not_error/
テーブルの内部的な結合処理について(HASH JOIN,MERGE JOIN)
http://vertica-tech.ashisuto.co.jp/join_type-2/
