0

This is once again a a question for SQL Pundits. I can create derived tables in Teradata using these approaches

 - With ( __,__)
(select statement ) alias 
query 
-- select ____ from a , ( select statement ) b  < Join condition )

I wondered in either of the cases HOW does the Data distribution ( for the DT ) take place . Is there a rule out here ? Is there some way I can tweak the DT so that I can get re-distribution based on the key(s) I want - the same functionality as VT But I guess, it avoids having a new CT statement and also you loose stats facility ( which I can in some situations afford to loose if its not changing the explain )

user1874594
  • 2,277
  • 1
  • 25
  • 49
  • your sample query is using with , are you creating CTE using WITH clause . You are asking about DT , but in DT with and recursion is not supported. Please read here http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1146_111A/ch01.032.128.html – anwaar_hell Oct 20 '15 at 04:50

1 Answers1

1

EXPLAIN the query and you will see the distribution, it's not the keys you want, it's usually based on the following join, e.g. distributed by the hash code of (your join columns).

Using a VT you can force a specific order, so of course there are cases were a Volatile Table might be better, but unless you need the same result in subsequent queries you should check the optimizer's plan using DTs/CTEs first.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Thanks Dieter. Here another one that was a bit "drainy" because here I redistributed by dims as many times its joined to fct. e.g. f1.c1=d1.c1 and f1.c2=d1.c1 and a LOT of these are LOJ and some are IJ's but different fct column with the SAME dimension column referring to one below – user1874594 Oct 21 '15 at 00:12
  • http://stackoverflow.com/questions/33231402/multiple-table-aliases-between-fact-and-dimension-teradata-sql-joins-sql-tuni – user1874594 Oct 21 '15 at 00:12