CODEWITHSUNDEEP
sqlperformanceteradatasql-tuningderived-table
user1874594
user1874594

Reputation: 2493

Teradata SQL Derived table data redistribution

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 )

Upvotes: 0

Views: 451

Answers (1)

dnoeth
dnoeth

Reputation: 60482

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.

Upvotes: 1

Related Questions