17.00 - 17.05 - 入れ子結合を使用した結合計画 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLリクエストおよびトランザクション処理

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
2020年6月
Content Type
プログラミング リファレンス
ユーザー ガイド
Publication ID
B035-1142-170K-JPN
Language
日本語 (日本)

入れ子結合を使用する結合計画について

入れ子結合を使用した場合、最適化ルーチンで次の結合計画が生成されます。

操作 結合テーブル 処理時間合計
Spool 3: 入れ子結合 (Hashed table_1, index table_2) 0.24秒間
Spool 1: 行ID結合 (Hashed spool_3, direct table_2) 0.22秒間

実行時間の見積もりは、0.46秒です。

性能向上係数の見積もりは、9782です。

最適化する結合計画のEXPLAIN出力

次の部分的EXPLAIN出力が生成されます。

2) Next, we do an all-AMPs RETRIEVE step from test.tab1 by way of an
   all-rows scan with no residual conditions into Spool 2, which is
   redistributed by hash code to all AMPs. Then we do a SORT to order
   Spool 2 by row hash. The size of Spool 2 is estimated to be 2 rows.
   The estimated time for this step is 0.06 seconds.
3) We do a all-AMP JOIN step from Spool 2 (Last Use) by way of an
   all-rows scan, which is joined to test.tab2 by way of unique
   index #4 "test.tab2.y2 = test.tab1.y1" extracting row ids only.
   Spool 2 and test.tab2 are  joined using a nested join. The result
   goes into Spool 3, which is redistributed by hash code to all AMPs.
   Then we do a SORT to order Spool 3 by row hash. The size of Spool 3
   is estimated to be 2 rows. The estimated time for this step is 0.18
   seconds.
4) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an
   all-rows scan, which is joined to test.tab2. Spool 3 and test.tab2
   are joined using a row id join. The result goes into Spool 1, which
   is built locally on the AMPs. The size of Spool 1 is estimated to be
   2 rows. The estimated time for this step is 0.22 seconds.