16.20 - Step Building Logic for Recursive Queries - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

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.