Example of a General Method for Maintaining a Join Index During UPDATE Operations
Note the following items in the EXPLAIN output:
| This step … | Does this … |
|---|---|
| 5 | reproduces the affected portion of the join index rows. |
| 6.1 | deletes the corresponding rows from the join index. |
| 6.2 | reproduces the affected portion of the join index rows and makes the necessary modifications to form the new rows. |
| 7.1 | inserts the newly modified rows into the join index. |
EXPLAIN UPDATE lineitem SET l_extendedprice=l_extendedprice * .80 WHERE l_partkey=50;
Part of the EXPLAIN output is shown below.
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.