Select Statement  «Prev 

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.

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:
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`

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`.
The output will represent the hierarchical structure:
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.