Search code examples
teradatacommon-table-expression

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?

Solution

  • They are almost not comparable because they're such different objects.

    A Volatile table is an actual table storing actual data. It is created just like a regular table, but with the VOLATILE keyword (and other gotchas). It and all the data stored in it, disappears when the session is over.

    A CTE on the other hand is more like a view. It doesn't store any data. You can think of it as a symbol that stands in for whatever is defined in it.

    For instance:

    WITH CTE myCTE (SELECT * FROM sometable WHERE id=3)
SELECT * 
FROM myCTE
WHERE myCTE.name in (SELECT max(name) FROM myCTE);

    Is just the same as:

    SELECT * 
FROM (SELECT * FROM sometable WHERE id=3) myCTE
WHERE myCTE.name in (SELECT max(name) FROM (SELECT * FROM sometable WHERE id=3) myCTE);

    Where we just replace out the symbol "myCTE" with the select statement hanging out in side of it.

    So, when you ask which will perform better... there is no way to answer that without knowning the exact situation where you are attempting to use a volatile table vs a CTE.

    Just remember, a Volatile table has to be created and loaded with data before it's queried. A CTE is just a view... so if it takes longer to query through your CTE than it would to create, load, and query the volatile table, then... go with the CTE. If you are going to repeatedly hit the same set of data over and over again in a procedure and you need a place to temporarily store that data set, then go with the volatile table.