Step Building Logic for Recursive Queries | Teradata Vantage - Step Building Logic for Recursive Queries - Advanced SQL Engine - Teradata Database

The 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;