All Forums Database
tiniwings 3 posts Joined 04/16
29 Apr 2016
Diff between CTE and Volatile tables in Teradata

Can anyone please explain difference between Common Table Expression(CTE)Tables and Volatile tables in which scenario they perform better.
As we can create & drop volatile tables as part of our SQL, where exactly CTE will perform better while compare to Volatile tables?
Thank You.

DaveWellman 66 posts Joined 01/06
30 Apr 2016

Hi,
Obvious differences first:
- you have to issue a 'create volatile table' command for a vt: a cte is created automatically by the dbms
- the table and content last until you logoff or drop the table: the cte and content last for the duration of the query (and possibly is removed before the final result set is built - it is after all built as a spool file)
- if you need statistics on the content in order to make subsequent processing efficient then you have to use a VT.
- if building a cte leaves you with horribly skewed data then you have to use a VT so that you can control that using the PI
Assuming that you still have a choice, then...
Performance differences:
Any performance difference mainly comes down to how often you are going to use the content. Start with a simple case:
Assume that it takes 1 second to run 'create volatile table' and 10 seconds to populate the vt. Assume that building the cte will also take 10 seconds.
If you are only going to use the content once then: using cte = 10 seconds, using vt = 11 seconds
If you are going to use the content 100 times (during a single session) then: using cte = 10 x 100 = 1000 seconds, using a vt  = 11 seconds
Does that help?
Regards,
Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

ToddAWalter 316 posts Joined 10/11
30 Apr 2016

One advantage of the CTE is that the optimizer sees it as part of the query just as if it was specified in the FROM clause or if the query was packaged in a view and then referenced in the FROM. This means that it is not at all automatic that the whole CTE will be spooled. If the optimizer sees an opportunity to join in place to the table in the CTE it will do so. If it sees an opportunity to to transitive closure with expressions from the CTE it will do so. It is fully optimized into the rest of the query. 
 
I agree with Dave that if a common query result is to be reused many times it will often be a good idea to materialize it in a volatile or even permanent table. But it is possible that would not always be true if the execution plan chosen by OPT can take advantage of having the knowledge from having the CTE in the query and come up with a plan that does not materialize the CTE query result at all. 

dnoeth 4628 posts Joined 11/04
30 Apr 2016

A Common Table Expression is the same as a Derived Table, the only difference is the place where it's defined and reusability (CEs can be used multiple times, but a DT must be repeated).
Some people prefer the top-down approach with CTEs over the bottom-up with DTs, you don't have to spot the deepest nested DT. Of course, as Teradata's CTE-implementation doesn't follow Standard SQL this advantage is lost and it's bottom-up again :(
 

Dieter

tiniwings 3 posts Joined 04/16
03 May 2016

Thanks all for the detailed clarification.
@Dave Your example clarifies a lot of confusion I am having about CTE.

If you are only going to use the content once then: using cte = 10 seconds, using vt = 11 seconds
If you are going to use the content 100 times (during a single session) then: using cte = 10 x 100 = 1000 seconds, using a vt  = 11 seconds

Thank You,
Satish
 

You must sign in to leave a comment.