UPDATE操作の実行時に結合インデックスを保守するための一般的な方法の例
EXPLAIN出力では、次の項目に注意してください。
ステップ | 実行内容 |
---|---|
5 | 結合インデックス行の影響を受ける部分を再生成する。 |
6.1 | 結合インデックスから対応する行を削除する。 |
6.2 | 結合インデックス行の影響を受ける部分を再生成し、新しい行を生成するために必要な変更を行ないます。 |
7.1 | 新しく変更された行を結合インデックスに挿入します。 |
EXPLAIN UPDATE lineitem SET l_extendedprice=l_extendedprice * .80 WHERE l_partkey=50;
EXPLAIN出力の一部を次に示します。
4) We do an all-AMPs RETRIEVE step in TD_MAP1 from DB1.ORDERJOINLINE by way of an all-rows scan with a condition of ( "DB1.ORDERJOINLINE.l_partkey = 50") into Spool 1 (all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 1 row (53 bytes). The estimated time for this step is 0.15 seconds. 5) We do an all-AMPs RETRIEVE step in TD_Map1 from Spool 1 by way of an all-rows scan into Spool 2 (all_amps), which is redistributed by the hash code of (DB1.ORDERJOINLINE.l_orderkey) to all AMPs in TD_Map1. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 is estimated with no confidence to be 1 row (53 bytes). The estimated time for this step is 0.07 seconds. 6) We execute the following steps in parallel. 1) We do an all-AMPs MERGE DELETE to DB1.ORDERJOINLINE from Spool 2 (Last Use). The size is estimated with no confidence to be 1 row. The estimated time for this step is 4.60 seconds. 2) We do an all-AMPs RETRIEVE step in TD_Map1 from Spool 1 (Last Use) by way of an all-rows scan into Spool 3 (all_amps), which is redistributed by the hash code of ( DB1.ORDERJOINLINE.l_orderkey) to all AMPs in TD_Map1. Then we do a SORT to order Spool 3 by join index. The size of Spool 3 is estimated with no confidence to be 1 row (53 bytes). The estimated time for this step is 0.16 seconds. 7) We execute the following steps in parallel. 1) We do an all-AMPs MERGE step in TD_MAP1 into DB1.ORDERJOINLINE from Spool 3 (Last Use). The size is estimated with no confidence to be 1 row. The estimated time for this step is 1.34 seconds. 2) We do an all-AMPs UPDATE step in TD_MAP1 from DB1.lineitem by way of an all-rows scan with a condition of ( "DB1.lineitem.l_partkey = 50"). The size is estimated with no confidence to be 2 rows. The estimated time for this step is 0.10 seconds. 8) We spoil the parser's dictionary cache for the table. 9) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> No rows are returned to the user as the result of statement 1.