Hierarchical retrieval using CONNECT BY

Question: How do I execute hierarchical retrieval using CONNECT BY in Oracle?
To execute hierarchical retrieval using CONNECT BY in Oracle, you can use a recursive SQL query^[1] that uses the CONNECT BY clause to specify the hierarchical relationship between the rows in the table. The CONNECT BY clause defines the parent-child relationship by specifying the column that identifies the parent row and the column that identifies the child row. Here are the steps to execute hierarchical retrieval using CONNECT BY in Oracle:

1. Define the table: Create a table that contains the data you want to retrieve hierarchically. The table should have a column that identifies the parent row and a column that identifies the child row.
2. Write the query: Write a recursive SQL query that uses the CONNECT BY clause to specify the hierarchical relationship between the rows in the table. The query should include the following elements:
   
   • The SELECT statement: This selects the columns you want to retrieve from the table.
   • The FROM clause: This specifies the table you want to retrieve data from.
   • The CONNECT BY clause: This specifies the parent-child relationship between the rows in the table.
   • The START WITH clause: This specifies the starting row for the hierarchy.
3. Execute the query: Run the SQL query in Oracle to retrieve the data hierarchically.
   Here is an example of a query that retrieves hierarchical data using CONNECT BY:
   

   SELECT employee_id, manager_id, first_name, last_name
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id
   START WITH manager_id IS NULL;
   

   
   In this example, the query retrieves the employee_id, manager_id, first_name, and last_name columns from the employees table. The CONNECT BY clause specifies that the employee_id column is the child row and the manager_id column is the parent row. The PRIOR keyword specifies that the parent-child relationship is between the employee_id and manager_id columns. The START WITH clause specifies that the hierarchy starts with the rows where manager_id is null.
By following these steps, you can execute hierarchical retrieval using CONNECT BY in Oracle.

When one record is related to another record in the same table, you have a recursive relationship. This kind of relationship is often called hierarchical^[2]. A simple example of a hierarchical relationship is a family tree. In fact, because this is such an intuitive way of understanding a hierarchy, the relationship is often described as a parent/child relationship. A parent/child list is produced that lists all the "children" items under their "parent" item.
In our sample pet store schema, you can see an example of a hierarchical relationship in the PRODUCT table. Some of the products are actually collections of other products. For example, the product called the Kitty Package is made up of a cat, a box of cat food, a bag of catnip, and a cat collar. Look at the following series of images below to see how the hierarchical relationship between the Kitty Package product and its four products works.

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 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. 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.

The `CONNECT BY` clause in Oracle SQL is used for hierarchical retrieval of data. It is particularly useful when you have a parent-child relationship represented in a single table and you want to retrieve rows in a hierarchical order. The `CONNECT BY` clause is paired with `START WITH` to specify the root of the hierarchy and is used to define how the parent-child relationship is represented in the table. Here's a simple example to illustrate. Consider a table called `employees` that has the following structure:

CREATE TABLE employees (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(50),
  manager_id NUMBER
);

In this table, the `manager_id` column indicates who the manager of each employee is. Let's populate the table with some sample data:
Example 3.3:

INSERT INTO employees (id, name, manager_id) VALUES (1, 'CEO', NULL);
INSERT INTO employees (id, name, manager_id) VALUES (2, 'CTO', 1);
INSERT INTO employees (id, name, manager_id) VALUES (3, 'Engineer1', 2);
INSERT INTO employees (id, name, manager_id) VALUES (4, 'Engineer2', 2);
INSERT INTO employees (id, name, manager_id) VALUES (5, 'CFO', 1);
INSERT INTO employees (id, name, manager_id) VALUES (6, 'Accountant', 5);

Here, the CEO (ID 1) manages the CTO (ID 2) and CFO (ID 5). The CTO manages Engineer1 (ID 3) and Engineer2 (ID 4). The CFO manages the Accountant (ID 6).
Using `CONNECT BY`in example 3.3:
To retrieve the hierarchical data, starting from the CEO:

SELECT LPAD(' ', LEVEL * 3 - 3) || name AS name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id
ORDER SIBLINGS BY name;

In this query:

1. `START WITH manager_id IS NULL` specifies that the root node(s) of the hierarchy are the rows where `manager_id` is `NULL` (i.e., the CEO).
2. `CONNECT BY PRIOR id = manager_id` specifies the parent-child relationship between rows. The `PRIOR` keyword refers to the parent row.
3. `LEVEL` is a pseudo-column provided by Oracle that returns the level number of a node in a tree structure (the root is level 1).
4. `LPAD(' ', LEVEL * 3 - 3)` is used for indentation, to make the hierarchy visually clear.
5. `ORDER SIBLINGS BY name` sorts siblings alphabetically by their `name`.

CEO
   CFO
      Accountant
   CTO
      Engineer1
      Engineer2

This shows that the CEO is at the top of the hierarchy, followed by the CFO and CTO. Each of them has their own subordinates, indicated by the indented names.

One of the challenges in working with hierarchical relationships is constructing a query that lists the parent with its children. This is particularly difficult if the relationship has more than one level. Fortunately, Oracle has come up with a special feature you can use to write a query that uses the hierarchy tree to pull data out of the table. Here is the general syntax:

SELECT column_name, column_name, ...
FROM table_name
CONNECT BY PRIOR parent_column_name = 
 child_column_name
START WITH condition;

The condition can be any valid condition you find in a WHERE clause. For example, if you only want to see what is in the Puppy Package, this query will do it:

SELECT PRODUCT_NAME, PRODUCT_ID
FROM PRODUCT
CONNECT BY PRIOR PRODUCT_ID = PACKAGE_ID
START WITH PRODUCT_NAME = 'Puppy Package';

The special phrases, CONNECT BY, PRIOR, and START WITH are used especially for a hierarchical query.

Oracle also lets you see the hierarchy level by adding the LEVEL pseudocolumn to your query. The figure below shows the query and the results.

In the next lesson, you will compare the INTERSECT, MINUS, and UNION commands.

Click the Exercise link below to practice writing queries.
Hierarchical Retrieval - Exercise

[1] recursive SQL Query: A recursive SQL query in Oracle is a type of query that iteratively processes data by referencing itself within the query. This allows you to traverse hierarchical structures or model repetitive operations on datasets.

[2] Hierarchical query: A form of query that retrieves data according to a hierarchy defined with a recursive relationship on a table.