How to optimize queries that have aggregation involving multiple fact tables

Database
Enthusiast

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

2 REPLIES
Enthusiast

Re: How to optimize queries that have aggregation involving multiple fact tables

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 .

Enthusiast

Re: How to optimize queries that have aggregation involving multiple fact tables

Hi Sieger

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