WITH name [(column, ...)] AS [/*+ no_inline|materialize */] (query expression) ...WITH clause
Teiid provides access to common table expressions via the WITH clause.
You can reference WITH clause items as tables in subsequent WITH clause items, and in the main query.
You can think of the WITH clause as providing query-scoped temporary tables.
Usage
Syntax rules
-
All of the projected column names must be unique. If they are not unique, then the column name list must be provided.
-
If the columns of the WITH clause item are declared, then they must match the number of columns projected by the query expression.
-
Each WITH clause item must have a unique name.
-
The optional
no_inlinehint indicates to the optimizer that the query expression should not be substituted as an inline view where referenced. It is possible with no_inline for multiple evaluations of the common table as needed by source queries. -
The optional
materializehint requires that the common table be created as a temporary table in Teiid. This forces a single evaluation of the common table.
|
Note
|
The WITH clause is also subject to optimization and its entries might not be processed if they are not needed in the subsequent query. |
|
Note
|
Common tables are aggressively inlined to enhance the possibility of pushdown.
If a common table is only referenced a single time in the main query, it is likely to be inlined.
In some situations, such as when you use a common table to prevent n-many-processing of a
non-pushdown, correlated subquery, you might need to include the no_inline or materialize hint.
|
Examples
WITH n (x) AS (select col from tbl) select x from n, n as n1WITH n (x) AS /*+ no_inline */ (select col from tbl) select x from n, n as n1Recursive common table expressions
A recursive common table expression is a special form of a common table expression that is allowed to refer to itself to build the full common table result in a recursive or iterative fashion.
Usage
WITH name [(column, ...)] AS (anchor query expression UNION [ALL] recursive query expression) ...The recursive query expression is allowed to refer to the common table by name. The anchor query expression is executed first during processing. Results are added to the common table and are referenced for the execution of the recursive query expression. The process is repeated against the new results until there are no more intermediate results.
|
Important
|
Non-terminating, recursive common table expressions can lead to excessive processing. |
By default, to prevent runaway processing of a recursive common table expression, processing is limited to 10000 iterations.
Recursive common table expressions that are pushed down are not subject to this limit, but could be subject to other source-specific limits.
You can modify the limit by setting the session variable teiid.maxRecursion to a larger integer value.
After the limit is exceeded, an exception is thrown.
The following example fails, because the recursion limit is reached before processing completes.
SELECT teiid_session_set('teiid.maxRecursion', 25);
WITH n (x) AS (values('a') UNION select chr(ascii(x)+1) from n where x < 'z') select * from n