CREATE TABLE with CTE statement

user_2085376 0
Viewed 2319

Is it possible to create a table from a query with a CTE statement? Something like:

CREATE TABLE db1.test1 AS
(
  WITH cte1(v1) as
  ( SEL v1 FROM db1.table1 )

  SEL * FROM cte1

)

This is how the CTE's look like:

WITH employees(id, name, boss, senior_boss) AS
(
SEL
empls.id,
empls.name,
supervisors.name as boss,
senior_bosses.name as senior_boss

FROM empl_cte AS empls
LEFT JOIN empl_cte AS supervisors ON empls.boss_id = supervisors.id
LEFT JOIN empl_cte AS senior_bosses ON supervisors.boss_id = senior_bosses.id
),

WITH empl_cte(....) AS
(

SEL
id, 
name
boss_id

FROM all_employees
WHERE <some_filters>
)

SEL

*

FROM products
LEFT JOIN employees ON products.sales_rep_id = employees.id

Both

  • converting the CTEs into views

and

  • converting employees as a sub-query (empl_cte as a VIEW) in the left join

leads to a massive loss of performance (run time blowing up from a couple of minutes to days of work). I can't figure out how Teradata optimizer works. EXPLAIN on the new refactored queries seem indicate that the LEFT JOIN becomes a product join draining countless of time.

teradata
1 Answers

This will work in V16 (and possibly earlier versions).

CREATE TABLE myTable AS (
    SELECT * FROM (
        WITH x AS (
            SELECT ...
            FROM  ...
            WHERE  ...
            ) 
        SELECT  ...
        FROM x ...
        WHERE  ...
        ) D
) WITH DATA PRIMARY INDEX (PK)
;

Basically you need to wrap the whole query, including the CTE, in a SELECT with an alias.

user_78836310
Related