 |
 |
Archives of the TeradataForum
Message Posted: Tue, 17 Dec 2002 @ 16:21:07 GMT
| Subj: | | Unnecessary spool step |
| |
| From: | | Kumaran Anantaraman |
Hi,
In the following explain plan, I see that spool 2 is created in step 1, which is then all-rows-scan read (last use), and put in spool 1
in step 3.1.
So, what is the purpose of step 3.1? Spool 1 and spool 2 are same, and step 2 could have directly created spool 1.
The explain plan below is exactly what I got with table names changed.
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock xxx_DB.yyy_TBL for access.
| |
| | 2) | Next, we do a SUM step to aggregate from xxx_DB.yyy_TBL by way of an all-rows scan with a condition of ("(xxx_DB.yyy_TBL.date_col >=
DATE '2002-12-02') AND(xxx_DB.yyy_TBL.date_col <= DATE '2002-12-04')"), and the grouping identifier in field 1030. Aggregate Intermediate
Results are computed globally, then placed in Spool 2. The input table will not be cached in memory, but it is eligible for synchronized
scanning. The size of Spool 2 is estimated with low confidence to be 101 rows.
| |
| | 3) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of an all-rows scan into Spool 1, which is built locally on the
AMPs. The size of Spool 1 is estimated with low confidence to be 101 rows. The estimated time for this step is 0.17 seconds.
| | |
| | |
| | 2) | We do a SUM step to aggregate from xxx_DB.yyy_TBL by way of an all-rows scan with a condition of ("(xxx_DB.yyy_TBL.date_col >= DATE
'2002-12-02') AND (xxx_DB.yyy_TBL.date_col <= DATE '2002-12-04')"), and the grouping identifier in field 1. Aggregate Intermediate Results
are computed globally, then placed in Spool 4. The input table will not be cached in memory, but it is eligible for synchronized scanning.
The size of Spool 4 is estimated with high confidence to be 793,963 rows.
| | |
| | 4) | We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of an all-rows scan with a condition of ("Field_4 > 0") into Spool 1,
which is built locally on the AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1. The size of Spool 1 is estimated
with high confidence to be 264,755 to 793,963 rows. The estimated time for this step is 0.66 to 1.56 seconds.
| |
| | 5) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 1 are sent back to the user as the result of statement 1.
| |
| |
