The Teradata Database parses recursive queries to identify the seed and recursive parts. The system defines the seed component to be all SELECT statements in the body of the recursive query that do not make any recursive references. By default, all that remains is defined to be the recursive component.
Consider the following recursive view definition as an example.
CREATE RECURSIVE VIEW tc (source, destin, carrier, depth) AS ( SELECT f.source, f.destin, f.carrier, 0 AS depth FROM flights AS f WHERE f.source = 'Paris' UNION ALL SELECT tcq.source, f.destin, f.carrier, tc.depth+1 AS depth FROM tc tcq, flights AS f WHERE tcq.destin=f.source AND depth <= 100 UNION ALL SELECT tcq.source, r.destin, 'EuroRail', tc.depth+1 AS depth FROM tc AS tcq, trains AS r WHERE tcq.destin=r.source AND depth <= 100 UNION ALL SELECT r.source, r.destin, 'EuroRail', 0 AS depth FROM trains AS r WHERE r.source = 'Kaiserslautern');
The seed query component of the view definition is highlighted in boldface text. The system treats all the rest of the SQL text as the recursive component of the definition.
Consider the following view definition as the foundation for examining how the Optimizer builds the steps to process a query made against it.
CREATE RECURSIVE VIEW reachable_from (destin, cost,depth) AS ( SELECT root.destin,root.cost, 0 AS depth FROM flights AS root WHERE root.source = 'paris' UNION ALL SELECT result.destin, seed.cost + result.cost, seed.depth + 1 AS newdepth FROM reachable_from AS seed, flights AS result WHERE seed.destin = result.source AND newdepth <= 20);
An EXPLAIN of the following query includes a RETRIEVE step from Root by way of the primary index "Root.Source = 'Paris'" JOIN step from Result with a join condition of ("Destin = Result.Source") AND "((Depth+1)<= 20)"
EXPLAIN SELECT * FROM reachable_from;
In the hypothetical EXPLAIN report:
- Spool 1 in the plan is used to compute the result of the steps built for the seed query in a RETRIEVE step from Root by way of the primary index "Root.Source = 'Paris'" .
- Next, the seed query steps result are fed into Spool 2, which then contains the final result.
- The recursive steps of the query are performed in a JOIN step from Result with a join condition of ("Destin = Result.Source") AND "((Depth+1)<= 20)".
It uses Spool 1 as the seed result and joins that to flights.
- The result of that join, Spool 3, is then treated as the next seed.
Spool 3 feeds into Spool 1 which is used again by the seed query step building process.
Note that Spool 1 is cleared by marking it as “last use”.
- The recursive steps are executed repeatedly until the result is empty.
- The retrieval of the rows from Spool 2 into Spool 4 produces the final result set that is returned to the requestor.