 |
 |
Archives of the TeradataForum
Message Posted: Fri, 01 Jul 2011 @ 10:07:05 GMT
| Subj: | | Spool being used in upi-pi join |
| |
| From: | | Anomy Anom |
<-- Anonymously Posted: Friday, July 01, 2011 05:33 -->
Hi,
The best way to join two tables is to join on pi s of both the tables as redistribution/duplication is not. However, even though I am joining
on pi s of both tables, I am able to see the following step(step num 4) in explain. Ideally step num 5 should start without step 4. Can someone
help me in understanding this pl ..
Explanation
-------------------------------------------------- | |
| | 4) | We do an all-AMPs RETRIEVE step from db.b by way of an all-rows scan with no residual conditions into Spool 2 (all_amps), which is built
locally on the AMPs. The size of Spool 2 is estimated with high confidence to be 1,156,066 rows (28,901,650 bytes). The estimated time for this
step is 16.34 seconds.
| |
| | 5) | We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a RowHash match scan, which is joined to db.a by way of a RowHash match scan
with no residual conditions. Spool 2 and db.a are joined using a merge join, with a join condition of ("db.a.upi = pi"). The input table db.a
will not be cached in memory. The result goes into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated
with low confidence to be 4,096,634 rows (184,348,530 bytes). The estimated time for this step is 36.54 seconds.
| |
Thank You
| |
