Hierarchical retrieval using CONNECT BY

Hierarchical Queries

A hierarchy is built upon a parent-child relationship within the same table or view.
Hierarchical query is one of the very first features of the Oracle Database. In a traditional query, Oracle looks for a good execution plan and retrieves the rows one after the other, in no specific order. In a hierarchy, the rows are organized as a tree, and I do not mean oak tree, I mean binary tree.

SYS_CONNECT_BY_PATH (column, char)

DESCRIPTION SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row returned by the CONNECT BY condition.

Hierarchical Profiler

For users connecting to Oracle Database 11g the PL/SQL Hierarchical Profiler provides the ability of identifying points of contention, delays and hence the option to review and tune the code.

1) A new product record is added for a cat. — 1) A new product record is added for cat. It has a PRODUCT_ID of 6. At this point, the Package_ID column is null.

2) Three other products are added with unique PRODUCT_IDs.

3) A new product (kitty package) is added with a PRODUCT_ID of 20. — 3) A new product (Kitty Package) is added with a PRODUCT_ID of 20. To connect the parent (Kitty Package) with the children (Cat, Cat Food), the Kitty Package's PRODUCT_ID is added in PACKAGE_ID column of all 4 products.

4) The final result is shown here in the Product table: five products that can all be purchased individually. — 4) The final result is shown here in the PRODUCT table: Five Products that can all be purchased individually. Four of the products can also be purchased as part of a package, which is identified as the fifth product