1

I am stuck in a situation , where an impromptu report cannot be improved because IBM cognos will not support any kind of DDL including Volatile table creation in Teradata. The report query aggregations and in lists with some 5000 values . I know how to fix the inlist part using UDF and a derived table, but I'd like to do something equivalent of a Volatile table where I can redistribute based on my choice , without actually creating one. So what I'd like to tell the optimizer is "Pl join these 3 tables and redistribute them by this Key combination" and then join to the rest of the query. I wondered if creating a derived table joining these "targeted tables" and then joining the DT would help create that effect 

sel A.1, A.2, Sum ( C.1) , Sum ( D.1) , case when A.5 in ( In-list) then "string" else "string2" end , sum (...) more columns ...etc 
from 
A Join B on ____ join C on ______ Join D ____

filter conditions

My approach Based on analysis that tables C D E are skewed on their Spools I'd like this to take place 1st A join C join D Join E . It will prolly duplicate C D E redistribute by A's PI ....and then the rest of the join

 sel < condition as before>
    from 
     ( sel < column list> from A Join C Join D join E --with respective Join conditions ) dt Join F on ___ Join G on____

So what I want is two things

  • Sequence of table joins 1st A C D E are joined
  • the Dt created thus should have a PI based on the rest of the dimensions so that I can make sure of star join
user1874594
  • 2,277
  • 1
  • 25
  • 49

1 Answers1

2

If you place a DISTINCT or GROUP BY in your derived table, the optimizer is not able to fold the derived table into the joins that exist outside the derived table. It will force the optimizer to materialize the derived table in spool first. The derived table will be redistributed based on the join condition to the outer query.

You will need to test the impact of putting the GROUP BY or DISTINCT on your derived table since it eliminates certain decisions the optimizer might have otherwise taken that it costed as being more efficient.

Rob Paller
  • 7,736
  • 29
  • 26