All Forums Database
sieger007 5 posts Joined 09/15
16 Sep 2015
How to optimize queries that have aggregation involving multiple fact tables

There is  "poetic query" that takes forever to run
Kind of looks like this
sel
col1.tb1,
col2,tb1,
(case <condition involving tb1 and tb2>) as "Dcol1",
Sum ( col1.tb3),
sum (col2.tb3 ),
sum (col3.tb3)
etc
from
tb1 left outer join tb2 <condition> LOJ tb3 <conditions>
where tb1 condition and tb2 condition and tb3 condition
 
group by ( case <condition> , colx.tb2,coly.tb1
Problem is TB3 is a HUGE fact table.  The PI of the fact table is NOT included in the joins or Queries here.
What I have done so far is create a volatile table ( same pi ) and tried to materialize and use it. VT does not have any Sum or group by inside it. Just a vanila VT but it ONLY has cols used in the Qy  and same PI.
Its taking time to run but so far the results are not encouraging
HOW can I optimize these kinds of queries

sieger007 5 posts Joined 09/15
17 Sep 2015

Tuning Gurus .. SQL Pundits... Dieter & other  Teradata Masters...where are you all ( I heard from my other friends ... you are pretty active). Well thanks in advance for this one .

gskaushik 56 posts Joined 09/10
26 Sep 2015

Hi Sieger
 
Kindly do provide the conditons on all the three tables , can you alsio post the original query along with explain plan
 
 

Regards
Subramanian kaushik Gurumoorthy

You must sign in to leave a comment.