CONNECT_BY_ROOT

The CONNECT_BY_ROOT function in Oracle is used in hierarchical queries to retrieve the root value of the parent-child relationship for each row. It is typically used in CONNECT BY queries to refer to the root (or top-most) ancestor of a row in a hierarchy. This allows you to fetch the root value for each row even when traversing a multi-level hierarchy.

Key Points:

• CONNECT_BY_ROOT is a function that helps to get the root value (the value at the top of the hierarchy) for each row in a hierarchical query.
• It is especially useful when you need to reference the root of the hierarchy for each child in a recursive query.
• It is most commonly used with the CONNECT BY clause in Oracle SQL, which is used to navigate hierarchical data (parent-child relationships).
• Unlike PRIOR, which references the parent row, CONNECT_BY_ROOT references the root of the tree or hierarchy.

 

Syntax:

SELECT column_list, CONNECT_BY_ROOT root_column AS root_value

FROM table_name

START WITH condition

CONNECT BY PRIOR parent_column = child_column;

Explanation:

• CONNECT_BY_ROOT root_column: This fetches the value of the root_column (the column that represents the root or top-most ancestor) for each row in the hierarchy.
• START WITH: Specifies the starting point or root of the hierarchy.
• CONNECT BY PRIOR parent_column = child_column: Defines the parent-child relationship, where PRIOR refers to the parent row.

 

Example 1: Basic Use of CONNECT_BY_ROOT

Consider the following employees table with columns emp_id, emp_name, and manager_id:

SELECT emp_id, emp_name, manager_id, CONNECT_BY_ROOT emp_name AS root_name

FROM employees

START WITH manager_id IS NULL

CONNECT BY PRIOR emp_id = manager_id;

Explanation:

• CONNECT_BY_ROOT emp_name: This retrieves the name of the root employee (the top-most ancestor) for each employee in the hierarchy.
• START WITH manager_id IS NULL: Starts the query from the employee(s) who do not have a manager (typically the root of the hierarchy).
• CONNECT BY PRIOR emp_id = manager_id: Defines the parent-child relationship, where an employee’s manager_id refers to the emp_id of the manager.

Sample Output:

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

Explanation of the Output:

• Root Name (root_name): For each employee, the CONNECT_BY_ROOT function fetches the name of the root employee (in this case, Alice) in the hierarchy.
• Hierarchy: Bob, Charlie, David, Eve, and Frank all have Alice as their root, as Alice is the top-most ancestor for everyone.

 

Example 2: CONNECT_BY_ROOT with Multiple Columns

You can also use CONNECT_BY_ROOT with multiple columns to retrieve the root values of different columns in the hierarchy.

SELECT emp_id, emp_name, manager_id,

       CONNECT_BY_ROOT emp_name AS root_name,

       CONNECT_BY_ROOT manager_id AS root_manager_id

FROM employees

START WITH manager_id IS NULL

CONNECT BY PRIOR emp_id = manager_id;

Explanation:

• CONNECT_BY_ROOT emp_name: Retrieves the name of the root employee.
• CONNECT_BY_ROOT manager_id: Retrieves the manager_id of the root employee (if applicable).

Sample Output:

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

Explanation of the Output:

• Root Name and Root Manager: For each employee, the root name is always Alice, but for the root employee (Alice), the root_manager_id is NULL since Alice has no manager.

---

Example 3: Using CONNECT_BY_ROOT in a More Complex Hierarchical Query

Let's consider a table departments with columns dept_id, dept_name, parent_dept_id, where parent_dept_id refers to the parent department (self-referencing).

SELECT dept_id, dept_name, parent_dept_id,

       CONNECT_BY_ROOT dept_name AS root_dept

FROM departments

START WITH parent_dept_id IS NULL

CONNECT BY PRIOR dept_id = parent_dept_id;

Explanation:

• CONNECT_BY_ROOT dept_name: Fetches the name of the root department for each row.
• START WITH parent_dept_id IS NULL: Specifies that the query starts with the root department (where parent_dept_id is NULL).
• CONNECT BY PRIOR dept_id = parent_dept_id: Defines the parent-child relationship between departments.

Sample Output:

dept_id	dept_name	parent_dept_id	root_dept
1	Sales	NULL	Sales
2	North	1	Sales
3	South	1	Sales
4	East	2	Sales
5	West	2	Sales

Explanation of the Output:

• Root Department (root_dept): All departments (North, South, East, and West) have "Sales" as the root department because the root department in the hierarchy is "Sales".

 

Use Cases for CONNECT_BY_ROOT:

1. Referencing the Root Value: CONNECT_BY_ROOT is useful when you want to retrieve the root value of a hierarchy (for example, the top-most manager, department, or category) for each row.
2. Reporting Hierarchies: If you need to create a report that shows the root department, manager, or entity for each subordinate in the hierarchy, CONNECT_BY_ROOT is an effective tool.
3. Complex Hierarchical Structures: In complex hierarchical structures where the root value might be located at different levels, CONNECT_BY_ROOT ensures that you can always reference the top-most ancestor.

 

Performance Considerations:

• Data Size: While CONNECT_BY_ROOT can be very powerful, large hierarchical queries with deep recursion may be resource-intensive. Make sure your database is optimized with proper indexes, especially on the columns used in the CONNECT BY clause.
• Cycle Prevention: Use NOCYCLE if you suspect there may be cycles in the data. Cycles in hierarchical data can lead to infinite loops, and NOCYCLE ensures the query stops at the first cycle.

Example with NOCYCLE:

SELECT emp_id, emp_name, CONNECT_BY_ROOT emp_name AS root_name

FROM employees

START WITH manager_id IS NULL

CONNECT BY NOCYCLE PRIOR emp_id = manager_id;

 

Conclusion:

CONNECT_BY_ROOT is a powerful Oracle SQL function used to reference the root (top-most) ancestor of each row in a hierarchical query. It is particularly useful in parent-child relationships to ensure you can always retrieve the root value for each row, regardless of how deep it is in the hierarchy. By using CONNECT_BY_ROOT, you can generate reports that include hierarchical information about the root entity, such as the root department, manager, or category, alongside the child entities.