Teradataの実行計画(1) - 大量データの結合の計画
Teradataの実行計画について少しずつ整理していきます。
今回は大量データの結合の実行計画について。
こんなクエリは、どういう実行計画になっていればOKだろうか。
select * from t /* 1000万件のトランザクション */ inner join m /* マスタ */ on m.c1=t.c1
RowHash match scan ... using a merge join
m.c1とt.c1がそれぞのテーブルのPI(Primary Index)であれば、以下の実行計画が出てくるはずだ。
4) We do an all-AMPs JOIN step from TST.m by way of a RowHash match scan with no residual conditions, which is joined to TST.t by way of a RowHash match scan with no residual conditions. TST.m and TST.t are joined using a merge join, with a join condition of ( "TST.m.c1 = TST.t.c1"). The result goes into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 2,759,376 rows. The estimated time for this step is 44.38 seconds.
RowHash match scan ... using a merge join は、Teradataの結合処理中で最速の実行計画。
このプランなら問題なし。
TeradataはデータをPrimary Index順にソートして格納している。
なので Primary Index 同士で結合するならば、両テーブルの先頭からレコードを順番に取り出していきなりマージ処理できる*1。
実行してみると、トランザクションを単純に全件読むのとほとんど変わらない速さでクエリが終了する。
single partition hash join
小さなテーブルのPI列と、大きなテーブルの非PI列で結合する...例えば「巨大なトランザクションと、100件程度の組織マスタを組織コードで結合する」といったパターンは、single partition hash join に帰着できれば問題ない。
マスタ側の件数が十分に少なければ(数10万件ぐらいまでか)、以下のような実行計画が出てくる。
4) We do an all-AMPs RETRIEVE step from TST.m by way of an all-rows
scan with no residual conditions into Spool 2 (all_amps), which is
duplicated on all AMPs. The size of Spool 2 is estimated with
high confidence to be 8,340 rows. The estimated time for this
step is 0.02 seconds.
5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
all-rows scan, which is joined to TST.t by way of an all-rows scan.
Spool 2 and TST.t are joined using a single partition hash_ join,
with a join condition of ("c1 = TST.t.c1"). The
result goes into Spool 1 (group_amps), which is built locally on
the AMPs. The size of Spool 1 is estimated with no confidence to
be 2,483,439 rows. The estimated time for this step is 22.38
seconds.
4)で小さいマスタを全AMPにコピーし、5)でトランザクションとハッシュ結合する。
トランザクションを単純に全件読むのに比べて、せいぜい1-2割増しの時間でクエリが終了する。
マスタが大きすぎたら...
マスタが100万件もあれば、恐らく single partition hash join ではなく、以下のような hash join of N partitions ... になってしまうだろう。
4) We do an all-AMPs RETRIEVE step from TST.m by way of an all-rows
scan with no residual conditions into Spool 2 (all_amps) fanned
out into 8 hash join partitions, which is duplicated on all AMPs.
The size of Spool 2 is estimated with low confidence to be
2,607,120 rows. The estimated time for this step is 0.83 seconds.
5) We do an all-AMPs RETRIEVE step from TST.t by way of an all-rows
scan with a condition of ("NOT (TST.t.c1 IS NULL)") into
Spool 3 (all_amps) fanned out into 8 hash join partitions, which
is built locally on the AMPs. The size of Spool 3 is estimated
with no confidence to be 2,483,439 rows. The estimated time for
this step is 14.54 seconds.
6) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
all-rows scan, which is joined to Spool 3 (Last Use) by way of an
all-rows scan. Spool 2 and Spool 3 are joined using a hash join
of 8 partitions, with a join condition of ("c1 = c1").
The result goes into Spool 1 (group_amps), which is built locally
on the AMPs. The size of Spool 1 is estimated with no confidence
to be 2,483,439 rows. The estimated time for this step is 15.57
seconds.
ハッシュ結合とは、小さい方のテーブル(=マスタ)をメモリ上に展開してハッシュ表とし、それに対して大きい方(=トランザクション)をぶつけるものだが、マスタの件数が多すぎて全件メモリに載らない場合は、Teradataはマスタをメモリに載るサイズに小分けし、トランザクションもそれに合わせて分割してから、複数回に分けて結合することで対応する。
上記の実行計画でやっていることは、
4)でマスタを8分割して全AMPにコピーし、
5)でトランザクションも8分割してスプールに書き出し、
6)で8回に分けて両者を結合している。
single partition hash joinに比べて、5)でトランザクションを「読んで書いて」する手間が増えているので遅い。
Teradataがどっちのhash joinを選択するかをアプリケーションからコントロールする方法はない(Teradataにhint句はない)。
ただし、ハッシュ表を展開するメモリのサイズはHTMemAllocというシステムパラメータになっているので、これを拡大して single partition hash join に倒れやすくする、ということはできるのかもしれない。やったことないけど。
