CONNECT BY PRIOR

In Oracle, the CONNECT BY PRIOR clause is used in hierarchical queries to traverse and retrieve data in a parent-child relationship, which is common in organizational structures, bill-of-materials, file directories, etc. It is most often used in conjunction with SELECT statements to retrieve data from tables that contain hierarchical relationships.

The CONNECT BY clause enables you to query hierarchical data structures where each row might have a reference to a parent row (typically, via a foreign key). The PRIOR keyword helps to establish the relationship between the parent and child rows in the hierarchy.

Syntax:

SELECT column_list

FROM table_name

START WITH condition

CONNECT BY PRIOR parent_column = child_column;

Key Components:

1. SELECT column_list: The columns you want to retrieve in the result set.
2. FROM table_name: The table that contains the hierarchical data.
3. START WITH condition: The condition to specify the root of the hierarchy, i.e., the starting point. This typically identifies the top-most parent or the root node of the tree.
4. CONNECT BY PRIOR parent_column = child_column: The relationship between parent and child rows, where the PRIOR keyword is used to reference the parent row's value.

Explanation:

• PRIOR: This keyword indicates that the relationship is established from the parent row to the child row. When you use PRIOR, it tells Oracle to reference the value of the parent row's column.
• START WITH: Defines the condition to identify the root node(s) of the hierarchy (i.e., where the parent-child relationship begins).
• CONNECT BY: Specifies the relationship for traversing the hierarchy, linking the parent to the child using a condition where the parent row’s value is compared with the child row’s value.

Example:

Consider a simple table employees with columns emp_id, emp_name, and manager_id where manager_id refers to the emp_id of the manager (self-referencing relationship):

 

emp_id	emp_name	manager_id
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	David	2
5	Eve	2
6	Frank	3

We can use the CONNECT BY PRIOR clause to get a hierarchical view of the employees reporting to each manager.

Example Query 1: Retrieve Employee Hierarchy

SELECT emp_id, emp_name, manager_id

FROM employees

START WITH manager_id IS NULL

CONNECT BY PRIOR emp_id = manager_id;

Explanation:

• START WITH manager_id IS NULL: This identifies Alice as the root of the hierarchy (because her manager_id is NULL).
• CONNECT BY PRIOR emp_id = manager_id: This defines the relationship between a parent (manager) and a child (employee). It compares the emp_id of the parent (manager) with the manager_id of the child (employee).

Output:

EMP_ID | EMP_NAME | MANAGER_ID

-------|----------|------------

1      | Alice    | NULL

2      | Bob      | 1

3      | Charlie  | 1

4      | David    | 2

5      | Eve      | 2

6      | Frank    | 3

Explanation of the Result:

• Alice (emp_id = 1) is the root node because her manager_id is NULL.
• Bob (emp_id = 2) and Charlie (emp_id = 3) report to Alice (emp_id = 1).
• David (emp_id = 4) and Eve (emp_id = 5) report to Bob (emp_id = 2).
• Frank (emp_id = 6) reports to Charlie (emp_id = 3).

Example Query 2: Retrieve Employee Hierarchy with Level Information

You can use the LEVEL pseudo-column to display the depth of each employee in the hierarchy (level of nesting).

SELECT emp_id, emp_name, manager_id, LEVEL

FROM employees

START WITH manager_id IS NULL

CONNECT BY PRIOR emp_id = manager_id

ORDER BY LEVEL;

Output:

EMP_ID | EMP_NAME | MANAGER_ID | LEVEL

-------|----------|------------|------

1      | Alice    | NULL       | 1

2      | Bob      | 1          | 2

3      | Charlie  | 1          | 2

4      | David    | 2          | 3

5      | Eve      | 2          | 3

6      | Frank    | 3          | 3

Explanation:

• LEVEL: This pseudo-column represents the depth or level of the row within the hierarchical query. Alice (the root) is at level 1, Bob and Charlie are at level 2, and David, Eve, and Frank are at level 3.

Example Query 3: Handling Cycles and Loops

In some cases, hierarchical data might contain cycles or loops, where a child points back to an ancestor. To prevent an infinite loop, you can use the NOCYCLE keyword.

SELECT emp_id, emp_name, manager_id

FROM employees

START WITH manager_id IS NULL

CONNECT BY NOCYCLE PRIOR emp_id = manager_id;

Explanation:

• NOCYCLE: Prevents the query from going into infinite recursion if a cycle is present in the hierarchical structure. If a cycle is detected, the query will stop processing and will not display those rows in the result.

Example Query 4: Retrieve Manager Hierarchy in Reverse Order

To traverse the hierarchy in reverse order (i.e., from child to parent), you can use CONNECT BY with the condition inverted.

SELECT emp_id, emp_name, manager_id

FROM employees

START WITH emp_id = 4

CONNECT BY PRIOR manager_id = emp_id;

Output:

EMP_ID | EMP_NAME | MANAGER_ID

-------|----------|------------

4      | David    | 2

2      | Bob      | 1

1      | Alice    | NULL

Explanation:

• START WITH emp_id = 4: The query starts with David (emp_id = 4), and then it finds his manager (Bob) and his manager’s manager (Alice).
• CONNECT BY PRIOR manager_id = emp_id: The direction of the relationship is reversed here; the manager’s ID is now compared to the employee’s ID.

Example Query 5: Filtering Hierarchy with CONNECT BY Conditions

You can use the CONNECT BY clause in conjunction with WHERE or AND to filter the hierarchy as needed.

SELECT emp_id, emp_name, manager_id

FROM employees

START WITH manager_id IS NULL

CONNECT BY PRIOR emp_id = manager_id

WHERE LEVEL <= 2;

Output:

EMP_ID | EMP_NAME | MANAGER_ID

-------|----------|------------

1      | Alice    | NULL

2      | Bob      | 1

3      | Charlie  | 1

Explanation:

• WHERE LEVEL <= 2: This limits the output to only the first two levels of the hierarchy. Alice, Bob, and Charlie are included, but David, Eve, and Frank are excluded because they are deeper in the hierarchy.

 

Performance Considerations:

• Complex Queries: When dealing with large datasets and deep hierarchies, CONNECT BY queries can be performance-intensive. Use indexes and optimize your queries to improve execution times.
• Recursion Limit: By default, Oracle allows up to 1000 levels of recursion. You can adjust this limit using the SET CONNECT BY parameter if necessary:

·        SET CONNECT BY ISOLATION LEVEL 1000;

• Indexing: If your hierarchical data is stored in a table with a self-referencing column (such as manager_id pointing to emp_id), indexing can help optimize the performance of CONNECT BY queries.