Archives of the TeradataForum
Message Posted: Tue, 01 May 2012 @ 20:41:01 GMT
Can and would anyone explain why the GROUP BY makes this query run better, even though it does not remove any duplicates? A similar question about 7 years ago someone suggested the GROUP BY could determine whether it was processed on all AMPS OR one AMP but I could not follow the logic exactly.
This query uses much more (10X) more spool space when the GROUP BY is omitted.
Number of records returned is exactly the same.
Explain plans are exactly the same - except an additional Aggregate when the GROUP BY is included
QUERY, RESULTS, AND EXPLAIN PLANS ARE BELOW: Many thanks for your time--a Teradata NooB.
SELECT A.Ban ,Active ,InService FROM (SELECT C.BAN ,MIN(CAST ( S. _FIRST_EFFECTIVE AS DATE) ) START FROM TABLEVIEWS.Customer C ,OTHER TABLEVIEWS.SUBSCRIBER S ,ANOTHERTABLEVIEW. SUBSCRIBER_HISTORY H WHERE C.CUSTOMER_ID = S.CUSTOMER_ID AND S.SUBSCRIBER_ID = H.SUBSCRIBER_ID AND S.SUBSCRIBER_TYPE_CD IN ("A","B") GROUP BY 1) A WHERE A.BAN < 100583252 --GROUP BY 1,2,3,4 ---COMMENTED OUTER GROUP BY THAT I AM PUZZLED BY---
RESULTS - Same number of records returned with the GROUP BY clause commented and uncommented
Uncommented -- 336,735 records returned. Peak spool 2,500,339,200. Time 23 seconds
Commented out --336,735 records returned. Peak spool 16,328,448,000. Time 21 seconds
The EXPLAIN plans are exactly the same - EXCEPT FOR STEP 6 WITH THE OUTER GROUP BY Step 1 - Lock Step2 - JOIN/SORT goes to Spool 4 - 531,000,000 bytes Step 3 - Join goes to spool 3 - 251,000,000 bytes Step 4 - Sum step (for inner group by) goes to spool 3 - 160,000,000 bytes Step 5 - retrieve goes to spool 1 - 150,000,000 bytes With outer GROUP BY step 6 --- We do an All-Amps SUM step to aggregate from Spool 1 (last use) . Aggregate intermediate results are placed on spool 9. The size of spool 9 is estimated to be 260,000,000 bytes. Step 7- retrieve. Into spool 7 -150,000,000 bytes Step 8 End Trans
WITHOUT OUTER GROUP BY: Step 6 ---retrieve into spool 7 - 150,000,000 bytes Step 7 - End trans
Thanks again -glenn
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|