Diff between CTE and Volatile tables in Teradata

Database

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.

Tags (2)
4 REPLIES
Senior Supporter

Re: Diff between CTE and Volatile tables in Teradata

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
Teradata Employee

Re: Diff between CTE and Volatile tables in Teradata

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. 

Junior Contributor

Re: Diff between CTE and Volatile tables in Teradata

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 :(

Re: Diff between CTE and Volatile tables in Teradata

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