Using Recursive Queries - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

A recursive query is a way to query hierarchies of data, such as an organizational structure, bill-of-materials, and document hierarchy.

Recursion is typically characterized by three steps:
  • Initialization
  • Recursion, or repeated iteration of the logic through the hierarchy
  • Termination
Similarly, a recursive query has three execution phases:
  • Initial result set
  • Iteration based on the existing result set
  • Final query to return the final result set

Ways to Specify a Recursive Query

You can specify a recursive query by:
  • Preceding a query with the WITH RECURSIVE clause.
  • Creating a view using the RECURSIVE clause in a CREATE VIEW statement.

For a complete description of the recursive query feature, with examples that illustrate how it is used and its restrictions, see Teradata Vantage™ - SQL Fundamentals, B035-1141.

For information on WITH RECURSIVE clause, see WITH Modifier.

For information on recursive views, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184.

Recursive Query Performance Considerations

Following are general guidelines regarding the performance impact of recursive queries:
  • Using a recursive query shows a significant performance improvement over using temporary tables with a stored procedures. In most cases, there is a highly significant improvement.
  • Using the WITH RECURSIVE clause has basically the same or equivalent performance as using the RECURSIVE VIEW.
  • In using a recursive query, it is important to put depth limits on the recursion to prevent infinite recursion when there are cycles in the underlying data.