WITH Clause
The Data Virtuality Server supports recursive and non-recursive common table expressions via the WITH clause. WITH
clause items may be referenced as tables in subsequent WITH
clause items and in the main query. The WITH
clauses can be thought of as providing query-scoped temporary tables.
Usage
WITH name [(column, ...)] AS [/*+ no_inline */] (query expression) ...
Syntax Rules
- All of the projected column names must be unique. If they are not unique, the column name list must be provided;
- If the columns of the
WITH
clause item are declared, they must match the number of columns projected by the query expression; - Each
WITH
clause item must have a unique name; - The optional
no_inline
hint indicates to the optimizer that the query expression should not be substituted as an inline view where referenced. It is possible withno_inline
for multiple evaluations of the common table as needed by source queries.
The WITH
clause is also subject to optimization, and its entries may not be processed if they are not needed in the subsequent query.
Examples
WITH n (x) AS (SELECT col FROM schema.tbl) SELECT x FROM n
WITH n (x) AS /*+ no_inline */ (SELECT col FROM schema.tbl) SELECT x FROM n
Recursive 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. Processing flows with the anchor query expression executed first. The results will be added to the common table and referenced for executing the recursive query expression. The process will be repeated against the new results until there are no more intermediate results.
To prevent runaway processing of a recursive common table expression, the processing is limited to 10000 iterations. Recursive common table expressions that are pushed down are not subject to this limit, but may be subject to other source-specific limits.
The limit can be modified by setting the session variable dv.maxRecusion
to a larger integer value. Once the maximum has been exceeded, an exception will be thrown.
Example
SELECT dv_session_set('dv.maxRecursion', 25);;
WITH n (x) AS (SELECT 'a' UNION SELECT chr(ascii(x)+1) FROM n WHERE x < 'z') SELECT * FROM n;;
This will fail to process as the recursion limit will be reached before processing completes. Please be aware that any value except a positive integer will get an exception.