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 ),
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
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 .
Kindly do provide the conditons on all the three tables , can you alsio post the original query along with explain plan