Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 01 Dec 2011 @ 15:37:38 GMT


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


Subj:   Joining on inequality involving a fact table
 
From:   Anomy Anom

<-- Anonymously Posted: Thursday, December 01, 2011 10:27 -->

GM Friends

I have this huge fact table that is joined to another table with a inequality condition involving aggregation . The entire query is a 20 table odd join query Of the format

     Col1,
     Case statement?.. Col20 ,
     Sum ( tb1.col1 X tb2.col2)
     etc

     From
     Tb1 Inner join TB2
     .LOJ Fact on Fact.date < TB12.date1 and Fact.date > Tb12.date2

     Inner join
     Fact.col1=Tb.col1
     Etc

     < Where clause >

TB are actually VIEWS , mostly straight select but some with views that have ?a more complex logic ?

The where clause selection is not strongly selective and only gets ?about <5% off the fact rows out . With all fact table data in place it just spools out, when ran initially via BO .

From the TD point of view there are a lot of things we can do in terms of correcting query logic , but, alas , these have to compatible with BO. e.g. BO universes use variables in where clauses so we cannot hard code these to make smaller materialized tables to join to .

I think most of the license I have is to change the views based on smaller tables to join to . So I can make new VW's with better joins in them or make some tables with data already in them but Not a lot of chance to play around with the logic Right now I brought it down to a stage where the redistribution part is avoided and there is just Duplication . But the most expensive step is the Sum Aggregation one.

My Q's here are

- Any Idea about what kind of 'tunables' other than the above can be implemented which are BO compatible . In other words some other tuning ideas that typically work with BO ?

- What can we do negotiate the Sum Aggregation step.


I am sure tuning pundits out there might have better instruments sitting in their drawer?..


Thank You Folks



     
  <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