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.