Joe Celko's Trees and Hierarchies in SQL for Smarties

Joe Celko's Trees and Hierarchies in SQL for Smarties

The Morgan Kaufmann Series in Data Management Systems
2004, Pages 169-174
Joe Celko's Trees and Hierarchies in SQL for Smarties

Chapter 9 - Proprietary Extensions for Trees

https://doi.org/10.1016/B978-155860920-4/50010-6Get rights and content

Publisher Summary

The chapter discusses proprietary extensions, such as Oracle's tree extensions, XDB tree extensions, IBM's DB2 database language, and Microsoft's extension for trees. It discusses the uses of Oracle's CONNECT BY PRIOR and START WITH clauses in the SELECT statement to provide partial support to reachability and path enumeration queries. The START WITH clause tells the engine the value of the root of the tree. The CONNECT BY PRIOR clause establishes the edges of the graph. The chapter also focuses on XDB System, which is an SQL product that runs on PC platforms and is fully compatible with DB2. The product has a set of extensions similar to those in Oracle, but this product uses functions rather than clauses to hide the recursion. The chapter also illustrates WITH operator from the SQL-99 standard, added by IBM to their DB2 product line to handle the need to factor out common subquery expressions and give them a name for the duration of the query. The other alternatives are repeating the code or creating a VIEW and use it. However, the VIEW is persistent in the schema after the query is done, unless it is explicitly dropped. The chapter also discusses EXPLODE operator, an extension proposed by John Tillquist and Feng-Yang Kuo, and Microsoft's extension.

References (0)

Cited by (0)

View full text