 |
 |
Archives of the TeradataForum
Message Posted: Sat, 12 Jun 2004 @ 15:30:49 GMT
| Subj: | | Re: Performance Improvement on this Query without Collecting Statistics |
| |
| From: | | Kambhoji, Naveen Ram Prasanna |
Hi Jon,
I have Pasted the Explain plan for your reference ..........
Explanation
-------------------------------------------------- | |
| | 5) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from AI_OLAP.dt by way of an all-rows scan with no residual conditions into Spool 4 (all_amps), which is
duplicated on all AMPs. Then we do a SORT to order Spool 4 by row hash. The size of Spool 4 is estimated with high confidence to be 17,020 rows.
The estimated time for this step is 0.05 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from AI_OLAP.i by way of an all-rows scan with no residual conditions into Spool 5 (all_amps), which is
built locally on the AMPs. Then we do a SORT to order Spool 5 by row hash. The result spool file will not be cached in memory. The size of
Spool 5 is estimated with low confidence to be 128,800,340 rows. The estimated time for this step is 14 minutes and 2 seconds.
| | |
| | 6) | We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a RowHash match scan, which is joined to Spool 5 (Last Use). Spool 4 and
Spool 5 are joined using a merge join, with a join condition of ("DateID = DateID"). The result goes into Spool 3 (all_amps), which is built
locally on the AMPs. The result spool file will not be cached in memory. The size of Spool 3 is estimated with index join confidence to be
128,800,340 rows. The estimated time for this step is 3 minutes and 55 seconds.
| |
| | 7) | We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by way of an all-rows scan, and the grouping identifier in field 1.
Aggregate Intermediate Results are computed globally, then placed in Spool 6. The aggregate spool file will not be cached in memory. The size of
Spool 6 is estimated with low confidence to be 128,800,340 rows. The estimated time for this step is 51 minutes and 8 seconds.
| |
| | 8) | We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way of an all-rows scan into Spool 1 (all_amps), which is redistributed by hash
code to all AMPs. Then we do a SORT to order Spool 1 by row hash. The result spool file will not be cached in memory. The size of Spool 1 is
estimated with low confidence to be 128,800,340 rows. The estimated time for this step is 14 minutes and 44 seconds.
| |
| | 9) | We do an all-AMPs MERGE into AI_OLAP.FA_BCWK_IMP from Spool 1 (Last Use).
| |
Let me know by this u can help me out.......BTW what is the use of 8th Step???
Thanx!
Prasanna.
| |
