Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 06 Apr 2004 @ 19:04:14 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Aggregating a derived table
From:   Victor Sokovin

  How can I improve the query so it doesn't run out of spool space?  

  It looks something like this:  

     > select count (t1.col1), sum (t1.col2), t1.col3
     > from
     > (Select Distinct col1, col2, col3, col4, col5
     > from table1) t1
     > Group by t1.col3

Before starting a serious thinking process, may I ask whether you tried to use GROUP BY instead of DISTINCT in the derived table defintiion?

     select count (t1.col1), sum (t1.col2), t1.col3
     (Select col1, col2, col3, col4, col5
     from table1
     group by col1, col2, col3, col4, col5) t1
     Group by t1.col3 ;

Sometimes this makes a difference with spool usage. Does it make a difference in your case?

The other way might be OLAP (window) forms of SUM and COUNT.



  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023