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

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

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