はじめに

Verticaでは、テーブルを結合する際、自動的に結合されるテーブルの順番を判断し、実行されます。
しかし、結合の際、最良なテーブルの順番が判断されずに、性能劣化が発生してしまうことがあります。
本記事では、その際に有効な対処である、テーブルの結合順番を強制する方法について紹介します。

結合(Hash Join)による動作

結合が行われる際、Hash Joinが選択された場合の基本的な動作は以下です。

1.結合する表の件数を比較し、件数の少ない方の表(以下table1)を外部テーブルとして全件読み取る
2.外部テーブルの結合条件キー列の値をハッシュ関数にかけてHash tableを作成する
3.内部テーブル(以下table2)の結合キー列を同じハッシュ関数で変換し、作成したHash tableを検索する
4.ハッシュが同じ列同士を結合し、結果を表示する

[イメージ図]

上記のように結合されるテーブルの順番(外部テーブル、内部テーブル)を自動で判断し、実行されます。
外部テーブル、内部テーブルの概要、判断基準は以下です。

外部テーブル：最初にアクセスされる表です。駆動表とも呼ばれます。基本は、小さいテーブルが指定されます。
内部テーブル：外部テーブルから参照される表です。基本は、大きいテーブルが指定されます。

性能劣化の原因と対処方法

原因

結合を行う際、件数が少ない方の表を外部テーブルとして読み込むことが理想ですが、

以下図のように、件数が多い表を外部テーブルとして読み込んでしまう場合があり、

その際は処理に大幅な時間が必要となる場合がございます。

[イメージ図]

対処方法

以下の手順にて各テーブルに数値を設定して重みづけをすることで、結合の順番を制御できます。

Step1：「EnableForceOuter」の有効化
Step2：テーブル毎に「FORCE OUTER」の値を指定
Step3：実行計画の確認

Step1：「EnableForceOuter」の有効化

データベースパラメーター「EnableForceOuter」を有効化することで、結合時、内部テーブル/外部テーブルの制御が可能になります。

SQL=> ALTER DATABASE <DB名> SET EnableForceOuter= 1 ;

Step2：テーブル毎に「FORCE OUTER」の値を指定

FORCE OUTERは結合時、どちらを内部テーブル、外部テーブルとするかの基準となる相対値です。
こちらの値をテーブル毎に指定します。

–FORCE OUTERの値が相対的に小さいテーブルは、結合時に内部テーブルとして指定されます。
–FORCE OUTERの値が相対的に大きいテーブルは、結合時に外部テーブルとして指定されます。

以下の例では、table1を外部テーブルとするために[8]、table2を内部テーブルとするため[3]を設定をします。

SQL=> ALTER TABLE table1 FORCE OUTER 8;
SQL=> ALTER TABLE table2 FORCE OUTER 3;

Step3：実行計画の確認

さいごに、実行計画から想定通りの外部テーブル、内部テーブルが指定されている事を確認します。
実行計画の確認方法はこちらをご参照ください。

[変更前]

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

[変更後]

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で確認しています。