I think it is an optimization done w.r.t the INSERT/SELECT statement. If the target table in the MSR involving the INSERT/SELECT is the
same table - then there is an optimization done in bringing the data generated by the different INSERT/SELECTS into a single spool and then
doing an ALL-AMP merge from that single spool into the target table. But if the target tables in the MSR's were to be different then I guess
this optimization will not be seen. This is what accounts for the result shown at the end of the MSR.
Here is an example which shows this optimization. An explain of the MSR also follows, which talks about the single spool that I have
mentioned.
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct CHK."pseudo table" for write on a RowHash to prevent global deadlock for CHK.t2.
| |
| | 2) | Next, we lock CHK.t2 for write.
| |
| | 3) | We do a single-AMP RETRIEVE step from CHK.t1 by way of the primary index "CHK.t1.a = 1" with no residual conditions into Spool 1
(one-amp), which is built locally on that AMP. The size of Spool 1 is estimated with low confidence to be 40 rows. The estimated time for
this step is 0.03 seconds.
| |
| | 4) | We do a single-AMP RETRIEVE step from CHK.t1 by way of the primary index "CHK.t1.a = 2" with no residual conditions into Spool 1
(one-amp), which is built locally on that AMP. The size of Spool 1 is estimated with low confidence to be 40 rows. The estimated time for
this step is 0.03 seconds.
| |
| | 5) | We do a single-AMP RETRIEVE step from CHK.t1 by way of the primary index "CHK.t1.a = 3" with no residual conditions into Spool 1
(one-amp), which is built locally on that AMP. The size of Spool 1 is estimated with low confidence to be 40 rows. The estimated time for
this step is 0.03 seconds.
| |
| | 6) | We do a SORT to order Spool 1 by row hash.
| |
| | 7) | We do an all-AMPs MERGE into CHK.t2 from Spool 1 (Last Use).
| |
| | 8) | 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. No rows are returned to the user as the result of statement 2. No
rows are returned to the user as the result of statement 3.
| |
Hope this helps.
