DWHPro Insights

Teradata Recursive Queries: Syntax and a Shortest-Path Example

By Roland Wenzlofsky ·

Teradata Recursive Queries: Syntax and a Shortest-Path Example

Teradata Recursive Queries

SQL provides two options for constructing a Teradata recursive query: utilizing the WITH RECURSIVE clause within a query or constructing a recursive view using the CREATE RECURSIVE VIEW statement.

A recursive query comprises 2 or 3 components, contingent on the preferred method among the aforementioned approaches:

  • The seed statement is the initial query that runs.
  • The recursive statement is the repeating query, executed until no further rows are returned. We usually add a termination condition to the recursive query to avoid infinite loops. Without a termination condition, the user may run out of spool space.
  • The final query returns the result of the seed query and all iterations of the recursive query.

Creating a recursive query using the WITH RECURSIVE clause:

WITH RECURSIVE <query>
(<columns>) AS
(
<seed query>
UNION ALL
<recursive query>
)
<final query>;

To create a recursive query, you can utilize a recursive view.

CREATE RECURSIVE VIEW <view>
(<columns>) AS
(
<seed query>
UNION ALL
<recursive query>
);

To better understand recursive queries on Teradata, I created a problem to tackle: determining the shortest paths in a graph.

Shortest Path – The Recursive Solution

All available legs will be stored in the table below:

CREATE SET TABLE Leg
(
From_Id INTEGER NOT NULL,
To_Id INTEGER NOT NULL
)
PRIMARY INDEX (From_Id);

/* Example Graph */

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book
INSERT into Leg VALUES ('1','2');
INSERT into Leg VALUES ('2','1');
INSERT into Leg VALUES ('2','3');
INSERT into Leg VALUES ('2','7');
INSERT into Leg VALUES ('3','2');
INSERT into Leg VALUES ('3','4');
INSERT into Leg VALUES ('3','5');
INSERT into Leg VALUES ('3','6');
INSERT into Leg VALUES ('6','7');
INSERT into Leg VALUES ('6','3');
INSERT into Leg VALUES ('7','2');
INSERT into Leg VALUES ('7','6');
INSERT into Leg VALUES ('7','8');
INSERT into Leg VALUES ('7','9');
INSERT into Leg VALUES ('8','7');
INSERT into Leg VALUES ('9','7');
INSERT into Leg VALUES ('5','3');
INSERT into Leg VALUES ('4','3');

The solution with a recursive query:

WITH RECURSIVE ThePath
(From_Id, To_Id, Path, TheLength) AS
(
SELECT
From_Id
,To_Id
,(TRIM(From_Id) || ',' || TRIM(To_Id)) (VARCHAR(512)) AS Path
, 1 AS TheLength
FROM
Leg
WHERE
From_Id = '1'
UNION ALL
SELECT
ThePath. From_Id
,t01.To_Id
,ThePath. Path || ',' || TRIM(t01.To_Id)
,ThePath. TheLength + 1 AS TheLength
FROM
Leg t01
INNER JOIN
ThePath
ON
t01.From_Id = ThePath. To_Id
WHERE POSITION(',' || TRIM(t01.To_Id) || ',' IN ',' || ThePath. Path || ',') = 0
-- Above WHERE condition ensures that we do not revisit a node a second time!
AND ThePath. TheLength < 100
-- Caps path depth only; path enumeration can still exhaust spool or exceed VARCHAR(512).
)



/* Below statement ensures that if there are multiple routes between two nodes, one of
the minimum number of stops is chosen */



SELECT
From_Id,
To_Id,
Path,
TheLength
FROM ThePath
QUALIFY ROW_NUMBER() OVER (PARTITION BY From_Id, To_Id ORDER BY TheLength, Path) = 1
ORDER BY 1,4,3;

Here is the result set of the query, showing all minimum distance routes from node 1

FROM_IDTO_IDPATHTheLength
121,21
131,2,32
171,2,72
141,2,3,43
151,2,3,53
161,2,3,63
181,2,7,83
191,2,7,93

Shortest Path – The Non-Recursive Solution

The following stored-procedure alternative is an incomplete, non-executable historical sketch. Its result-set declaration, object names, aliases and column lists need repair. It is not a tested performance alternative to the recursive query.

CREATE SET TABLE Route
(
From_Id INTEGER NOT NULL,
To_Id INTEGER NOT NULL,
Path VARCHAR(512),
TheLength INTEGER
) PRIMARY INDEX (From_Id);
REPLACE PROCEDURE ThePath()
DYNAMIC RESULT SETS
BEGIN
DELETE FROM ShortestPath;
INSERT INTO ShortestPath
SELECT
From_Id,
To_Id,
(TRIM(From_Hub_Id) || ',' || TRIM(To_Hub_Id)) (VARCHAR(512)) AS Path,
1 AS TheLength
FROM Leg
WHERE From_id = '1';

WHILE ACTIVITY_COUNT > 0 DO
INSERT INTO Route
SELECT
t02.From_Id
t01.To_Id
t02.Path || ‘,’ || TRIM(t01.To_Id)
t02.TheLength + 1
FROM Leg t01, Route t02
WHERE
t01.From_Id = t02.To_Id
AND t01.To_Id <> ‘1’
AND t02.TheLength =
(SELECT MAX(TheLength) FROM Route)
AND t01.To_Id NOT IN
(SELECT To_Id FROM Route)
AND B.TheLength < 200
QUALIFY ROW_NUMBER() OVER (PARTITION BY t01.To_Id ORDER BY t02.Path) = 1;
END WHILE;
BEGIN
DECLARE mycursor CURSOR WITH RETURN ONLY FOR
SELECT * FROM Route  ORDER BY 4, 3;
OPEN mycursor;
END;
END;

The speed of a solution cannot be generalized as it relies on the complexity of the accessed data structures.

The stored-procedure alternative is incomplete and unexecuted: result-set count, Route/ShortestPath naming, aliases and column lists require repair. Its efficiency is not established; do not copy it as a working procedure.

Multiple factors contribute to this phenomenon.

  • Our stored procedure keeps a routes table of all visited nodes, while the recursive query might revisit the same node several times.
  • The recursive query continues to iterate even after the shortest path between two nodes has already been found. Running additional recursive steps increases spool usage quickly.

Recursive templates require concrete names. The recursive graph query returned all eight published shortest paths, but path enumeration can grow exponentially and VARCHAR(512) can overflow. The bound must be < 100 to cap generated path length at 100; <= 100 generated 101 on a chain. The stored-procedure excerpts are non-executable historical sketches.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam