EXPLAINとは
実行プランをレポートするコマンド
構文
EXPLAIN [SQL文]
例１：全表スキャン
EXPLAIN select * from t1;
*** Help information returned. 13 rows.
*** Total elapsed time was 1 second.
Explanation
--------------------------------------------------------------------------------
1) First, we lock SYSDBA.t1 in TD_MAP1 for read on a reserved RowHash
to prevent global deadlock.
2) Next, we lock SYSDBA.t1 in TD_MAP1 for read.
3) We do an all-AMPs RETRIEVE step in TD_MAP1 from SYSDBA.t1 by way
of an all-rows scan with no residual conditions into Spool 1
(group_amps), which is built locally on the AMPs. The size of
Spool 1 is estimated with high confidence to be 4,194,309 rows (
411,042,282 bytes). The estimated time for this step is 11.31
seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 11.31 seconds.
解釈
- グローバルデッドロックを防ぐため、リザーブド行ハッシュのt1に読込ロックをかける。
- t1表に読込ロックをかける
- 条件無しの全行スキャンによりt1表から全てのAMPの取得ステップを実行し、Spool1領域に格納
Spool1領域は高い精度で420万行（411MB）
取得ステップの見積時間は11.31秒 - 最後に、トランザクション終了ステップを、リクエストの処理に関連した全てのAMPに送出
→Spool1の内容がステートメント1の結果としてユーザに対して返される。
わざわざスプール領域に一度書き出す理由は、「クライアントに対する最終結果セットは必ずスプールを作成する」ためらしい。
Teradataは単一テーブルから全件返すときもスプールを使う - 極北データモデリング
https://tgk.hatenadiary.org/entry/20100831/1283263387
例2：基本索引１行スキャン
EXPLAIN select * from t1 where c1 = 9;
*** Help information returned. 5 rows.
*** Total elapsed time was 1 second.
Explanation
--------------------------------------------------------------------------------
1) First, we do a single-AMP RETRIEVE step from SYSDBA.t1 by way of
the unique primary index "SYSDBA.t1.c1 = 9" with no residual
conditions. The estimated time for this step is 0.00 seconds.
-> The row is sent directly back to the user as the result of
statement 1. The total estimated time is 0.00 seconds.
解釈
- ユニーク基本索引による単一AMP取得ステップを実行
→（スプールに格納せず）直接ユーザに返す。
例３：基本索引複数行スキャン
EXPLAIN select * from t1 where c1 between 1 and 10;
*** Help information returned. 64 rows.
*** Total elapsed time was 1 second.
Explanation
--------------------------------------------------------------------------------
1) First, we do a single-AMP RETRIEVE step from SYSDBA.t1 by way of
the unique primary index "SYSDBA.t1.c1 = 1" with no residual
conditions into Spool 1 (group_amps), which is built locally on
that AMP. The size of Spool 1 is estimated with high confidence
to be 1 row (98 bytes). The estimated time for this step is 0.00
seconds.
2) Next, we do a single-AMP RETRIEVE step from SYSDBA.t1 by way of
the unique primary index "SYSDBA.t1.c1 = 2" with no residual
conditions into Spool 1 (group_amps), which is built locally on
that AMP. The size of Spool 1 is estimated with high confidence
to be 2 rows (196 bytes). The estimated time for this step is
0.00 seconds.
3) We do a single-AMP RETRIEVE step from SYSDBA.t1 by way of the
unique primary index "SYSDBA.t1.c1 = 3" with no residual
conditions into Spool 1 (group_amps), which is built locally on
that AMP. The size of Spool 1 is estimated with high confidence
to be 3 rows (294 bytes). The estimated time for this step is
0.00 seconds.
4) We do a single-AMP RETRIEVE step from SYSDBA.t1 by way of the
unique primary index "SYSDBA.t1.c1 = 4" with no residual
conditions into Spool 1 (group_amps), which is built locally on
that AMP. The size of Spool 1 is estimated with high confidence
to be 4 rows (392 bytes). The estimated time for this step is
0.00 seconds.
5) We do a single-AMP RETRIEVE step from SYSDBA.t1 by way of the
unique primary index "SYSDBA.t1.c1 = 5" with no residual
conditions into Spool 1 (group_amps), which is built locally on
that AMP. The size of Spool 1 is estimated with high confidence
to be 5 rows (490 bytes). The estimated time for this step is
0.00 seconds.
6) We do a single-AMP RETRIEVE step from SYSDBA.t1 by way of the
unique primary index "SYSDBA.t1.c1 = 6" with no residual
conditions into Spool 1 (group_amps), which is built locally on
that AMP. The size of Spool 1 is estimated with high confidence
to be 6 rows (588 bytes). The estimated time for this step is
0.00 seconds.
7) We do a single-AMP RETRIEVE step from SYSDBA.t1 by way of the
unique primary index "SYSDBA.t1.c1 = 7" with no residual
conditions into Spool 1 (group_amps), which is built locally on
that AMP. The size of Spool 1 is estimated with high confidence
to be 7 rows (686 bytes). The estimated time for this step is
0.00 seconds.
8) We do a single-AMP RETRIEVE step from SYSDBA.t1 by way of the
unique primary index "SYSDBA.t1.c1 = 8" with no residual
conditions into Spool 1 (group_amps), which is built locally on
that AMP. The size of Spool 1 is estimated with high confidence
to be 8 rows (784 bytes). The estimated time for this step is
0.00 seconds.
9) We do a single-AMP RETRIEVE step from SYSDBA.t1 by way of the
unique primary index "SYSDBA.t1.c1 = 9" with no residual
conditions into Spool 1 (group_amps), which is built locally on
that AMP. The size of Spool 1 is estimated with high confidence
to be 9 rows (882 bytes). The estimated time for this step is
0.00 seconds.
10) We do a single-AMP RETRIEVE step from SYSDBA.t1 by way of the
unique primary index "SYSDBA.t1.c1 = 10" with no residual
conditions into Spool 1 (group_amps), which is built locally on
that AMP. The size of Spool 1 is estimated with high confidence
to be 10 rows (980 bytes). The estimated time for this step is
0.00 seconds.
11) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.02 seconds.
解釈
- t1表から、ユニーク基本索引（c1=1）による単一AMP取得ステップを実行し、Spool1に格納。
Spool1は高い精度で1行（98バイト）になる。 - t1表から、ユニーク基本索引（c1=2）による単一AMP取得ステップを実行し、Spool1に格納。
Spool1は高い精度で1行（196バイト）になる。
（略） - リクエストの処理に関わった全てのAMPに対してトランザクション終了ステップを送出
→Spool1の内容がステートメント1の結果としてユーザに対して返される。
基本索引を利用した単一行取得を件数分実施したほうが早いとの判断。
単一行取得においては表に対するロックを取得しないものなのか？
例４：基本索引複数行スキャン（選択率：低）
EXPLAIN select * from t1 where c1 between 1 and 10000;
*** Help information returned. 13 rows.
*** Total elapsed time was 1 second.
Explanation
--------------------------------------------------------------------------------
1) First, we lock SYSDBA.t1 in TD_MAP1 for read on a reserved RowHash
to prevent global deadlock.
2) Next, we lock SYSDBA.t1 in TD_MAP1 for read.
3) We do an all-AMPs RETRIEVE step in TD_MAP1 from SYSDBA.t1 by way
of an all-rows scan with a condition of ("(SYSDBA.t1.c1 <= 10000)
AND (SYSDBA.t1.c1 >= 1)") into Spool 1 (group_amps), which is
built locally on the AMPs. The size of Spool 1 is estimated with
high confidence to be 5,001 rows (490,098 bytes). The estimated
time for this step is 1.25 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 1.25 seconds.
選択率が0.1%（5000行/420万行）でも例1の全表スキャンとほぼ同様の実行プラン
例５：基本索引複数行スキャン（選択率：中）
EXPLAIN select * from t1 where c1 between 1 and 1000;
*** Help information returned. 131 rows.
*** Total elapsed time was 1 second.
Explanation
--------------------------------------------------------------------------------
1) First, we lock SYSDBA.t1 in TD_MAP1 for read on a reserved RowHash
to prevent global deadlock.
2) Next, we lock SYSDBA.t1 in TD_MAP1 for read.
3) We do an INSERT step into Spool 2 with rows (c1) values (1000), (
999), (998), (997), (996), (995), (994), (993), (992), (991), (
990), (989), (988), (987), (986), (985), (984), (983), (982), (
981), (980), (979), (978), (977), (976), (975), (974), (973), (
972), (971), (970), (969), (968), (967), (966), (965), (964), (
963), (962), (961), (960), (959), (958), (957), (956), (955), (
（中略）
108), (107), (106), (105), (104), (103), (102), (101), (100), (99),
(98), (97), (96), (95), (94), (93), (92), (91), (90), (89), (88),
(87), (86), (85), (84), (83), (82), (81), (80), (79), (78), (77),
(76), (75), (74), (73), (72), (71), (70), (69), (68), (67), (66),
(65), (64), (63), (62), (61), (60), (59), (58), (57), (56), (55),
(54), (53), (52), (51), (50), (49), (48), (47), (46), (45), (44),
(43), (42), (41), (40), (39), (38), (37), (36), (35), (34), (33),
(32), (31), (30), (29), (28), (27), (26), (25), (24), (23), (22),
(21), (20), (19), (18), (17), (16), (15), (14), (13), (12), (11),
(10), (9), (8), (7), (6), (5), (4), (3), (2), (1), which is
redistributed by the hash code of (c1) to all AMPs.
4) We do an all-AMPs RETRIEVE step in TD_Map1 from Spool 2 (Last Use)
by way of an all-rows scan into Spool 3 (all_amps), which is built
locally on the AMPs. The size of Spool 3 is estimated with high
confidence to be 1,000 rows (17,000 bytes). The estimated time
for this step is 0.64 seconds.
5) We do an all-AMPs JOIN step in TD_Map1 from Spool 3 (Last Use) by
way of a RowHash match scan, which is joined to SYSDBA.t1 by way
of a RowHash match scan with no residual conditions. Spool 3 and
SYSDBA.t1 are joined using a single partition hash join, with a
join condition of ("c1 = SYSDBA.t1.c1"). The result goes into
Spool 1 (group_amps), which is built locally on the AMPs. The
size of Spool 1 is estimated with high confidence to be 501 rows (
49,098 bytes). The estimated time for this step is 0.42 seconds.
6) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1.
解釈
- デッドロックを防ぐため、t1表に対して、予約済「行ハッシュ」への読取ロックを取得
- t1表に対する読取ロックを取得
- Spool2にc1=1～1000の挿入ステップを実行。それらの値はc1の値によって全てのAMPへ再分配される。
- Spool2からの全AMP取得ステップを実行し、Spool3に格納。
- 全AMPに対する結合ステップを実行。（行ハッシュ突合によりt1とSpool3を結合）
Spool3とt1は、c1を条件として単一パーティションハッシュ結合し、Spool1に格納。 - 処理に関連する全てのAMPに対してトランザクション終了ステップを送出。
→Spoolの内容がステートメント1の結果としてユーザに返される。
選択率によって、選択される実行プラン。
例４と比較したメリットが不明。c1の値によっては再分配の結果、AMPを絞れる可能性があるからだろうか。