Data Management

Getting Recursive: A Quick Primer on Querying Hierarchical Data in Oracle Database 11g Release 2

Download Now Date Added: Feb 2010
Format: HTML

The Recursive database processing also known as the bill of materials or parts explosion problem is related to a range of application fields such as human resources, manufacturing, financial markets, and academia. The data that is involved in such processing is referred to as tree structured or hierarchical. Oracle Database 11g Release 2 mentioned in this paper supports recursion through subquery factoring that results in a new and potentially better way to deal with problem called querying hierarchical data. The new Recursive WITH Clause feature of Oracle Database 11g Release 2 offers a strong cycle-detection capability as well as the option of using depth first or breadth first traversal to process data. Recursive processing is the process of recovering data from a tree. Oracle Database supports recursive processing through the special clauses CONNECT BY and START WITH. CONNECT BY indicates the rows that are to be recovered in a tree-structured sequence. The starting point of a traversal is known as START WITH, which is determined by the seed in the initialization subquery. Recursion through subquery factoring requires a named subquery that is defined using a WITH clause and a query against that named subquery. All details and data provided in this paper are accurate and reliable. It also talks about a situation that can arise with hierarchical data that is called a cycle.