Archives of the TeradataForum

Message Posted: Tue, 01 May 2012 @ 20:41:01 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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.

-glenn

     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

	<Prev	Next>		<<First	<Prev	Next>	Last>>