Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 27 Mar 2008 @ 22:17:24 GMT


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


Subj:   Re: COLLECT STATISTICS on Indexed Columns &
 
From:   Mutsch, Joerg

Basically I would say yes and so does the manual of Teradata. Anyway there has been a posting regarding statistics on certain columns where the performance decreased (or was it about indexing ??? I am not sure). So make sure when you do collect statistics that you take a look on the explain.

Anyway what really helped me to increase performance is using derived tables within your query to aggregate the records as soon as possible and before you join other tables together (makes sense if you have huge fact tables)

Eg. a is representing the fact table, b is the lookup table

     Select b.field1, b.field2, sum(a.field3) as field3
      from Table1 a
     Left outer join Table2 b
     On a.field1=b.field1
     And b.field2=b.field2
     Where a.field1=123
     And a.field2=345
     Group by 1,2
     ;

With derived Table

     Select a.field1, a.field2, b.field3
      From
     (
       Select field1, field2, sum(a.field3) as field3
       From Table1
       Where field1=123
       And field2=345
       Group by 1,2
     )a
     Left outer join Table2 b
     On a.field1=b.field1
     And b.field2=b.field2
     ;

This technique helped me most of the time (I am dealing a lot with Front end tools) in combination of he statistics there is even no need to set secondary indices.

There are many more to mention, but I am sure you will receive additional comments from other forum members :)


Cheers

Joerg



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