Recursive query optimization

 

Certain applications and data are recursive by nature. Examples of such applications are a bill-of-material, reservation, trip planner or networking planning system where data in one results row has a natural relationship (call it a parent, child relationship) with data in another row or rows. Although the kinds of recursion implemented in these systems can be performed by using SQL Stored Procedures and temporary results tables, the use of a recursive query to facilitate the access of this hierarchical data can lead to a more elegant and better performing application.

Recursive queries can be implemented by defining either a Recursive Common Table Expression (RCTE) or a Recursive View.

• Recursive query example
  A recursive query is one that is defined by a Union All with an initialization fullselect that seeds the recursion and an iterative fullselect that contains a direct reference to itself in the FROM clause.

• Multiple initialization and iterative fullselects
  The use of multiple initialization and iterative fullselects specified in the recursive query definition allows for a multitude of data sources and separate selection requirements to feed the recursion process.

• Predicate Pushing
  When processing most queries with a non-recursive common table expressions or views, local predicates specified on the main query are pushed down so fewer records need to be materialized. Pushing local predicates from the main query in to the defined recursive part of the query (through the Union ALL), however, may considerably alter the process of recursion itself. So as a general rule, the Union All specified in a recursive query is currently a predicate fence and predicates are not pushed down or up, through this fence.

• Specifying SEARCH consideration
  Certain applications dealing with hierarchical, recursive data, may have a requirement in how data is processed: by depth or by breadth.

• Specifying CYCLE considerations
  Recognizing that data in the tables used in a recursive query might be cyclic in nature is important to preventing infinite loops.

• SMP and recursive queries
  Recursive queries can benefit as much from symmetric multiprocessing (SMP) as do other queries on the system.

 

Parent topic:

Processing queries: Overview