Archives of the TeradataForum

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

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

<-- 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>>