Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 26 Jan 2009 @ 13:46:27 GMT


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


Subj:   Some basic but interesting questions on COLLECT
 
From:   Srihari Duddukuru

Hi There,

I need some important inputs on the way we collect statistics. Below is the example

I have 4 tables, each with a single column Primary Index (assume all have same data volume ~ 5 million rows). Below are the tables and their PIs

     Table A (PI=x1)
     Table B (PI=y1)
     Table C (PI=y1)
     Table D (PI=x2)

And, here is the query

     Select A.x1..., B..., C...., D...
     from
     A, B, C, D
     where
          A.x1=b.x1
     and
     A.x2=D.x2
     and
     A.x3=100
     and
     A.x4='BMW'
     and
     B.y1=C.y1
     and
     b.y2 in ('SFO','LAX')
     and
     C.z1 = 11
     and
     C.z2 = '*'
     and
     D.r1 <> 'PAK'
     and
     D.r2 = '$135000';

Now, the queries follows

1. In the above query, what is the best way to collect stats? Is it on all columns individually? Like..

     STATS ON A (x1)
     STATS ON A (x2)
     STATS ON A (x3) ....

2. Is it good to collect stats on combination of joining columns and filter columns separately as given below?

Assume, we have stats on PI already

     STATS ON A (x1,x2)
     STATS ON A (x3,x4)
     STATS ON B (y2)
     STATS ON C (z1,z2)
     STATS on D (r1,r2)
     STATS on D (x2) ...

3. Or, combination of all columns of a table is good?

     STATS ON A (x1,x2,x3,x4)
     STATS ON B (y1,y2)
     STATS on C (z1,z2,y1)
     STATS on D (x2,r1,r2)

4. Since there is an inequality condition on D.r1 (D.r1 <> 'PAK'), how the stats should be?

5. Incase conditions the columns D.r1 and D.r2 are ORed together, then how the stats should be?

     ...
     and
     (
     D.r1 <> 'PAK'
     OR
     D.r2 = '$135000'
     )

In short, Is it always good to collect stats on individual columns or combination of columns based on joining and filter conditions? In case of later, what is the best way of grouping the columns?


Regards,

Hari



     
  <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: 27 Dec 2016