Archives of the TeradataForum

Message Posted: Wed, 20 Apr 2011 @ 11:14:43 GMT

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

<-- Anonymously Posted: Wednesday, April 20, 2011 06:57 -->

I have the following query which is taking 5.50 mins to execute

     SELECT  LIST_NAME,ORIGINAL_NAME, COUNT(*)
     FROM EMPLOYEE E, LIST L
     WHERE E.LIST_ID=L.LIST_ID
     GROUP BY LIST_NAME,ORIGINAL_NAME

Can someone help me in tuning the above query. Planning to use aggregate join index for this as users expect this query to run in approx 20 secs. To avoid overhead(insert s and update s), planning to create JI after every day s load is processed and then drop it just before next days load as utilities are not supported with JI s. Any inputs are much appreciated.

Explain plan:

     6) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by way
        of an all-rows scan , grouping by field1 (L.LIST_NAME, E.ORIGINAL_name).
        Aggregate Intermediate Results are computed globally, then placed in
        Spool 5.  The aggregate spool file will not be cached in memory.  The
        size of Spool 5 is estimated with no confidence to be 846,399 rows
        (9,496,596,780 bytes).  The estimated time for this step is 5 minutes
        and 30 seconds.

Thanks

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