Archives of the TeradataForum
Message Posted: Thu, 01 Dec 2011 @ 15:37:38 GMT
<-- Anonymously Posted: Thursday, December 01, 2011 10:27 -->
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 >
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
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|