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
(case <condition involving tb1 and tb2>) as "Dcol1",
Sum ( col1.tb3),
sum (col2.tb3 ),
sum (col3.tb3)
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

Subramanian kaushik Gurumoorthy

You must sign in to leave a comment.