CONNECT BY LEVEL

In Oracle, the LEVEL pseudo-column is commonly used in hierarchical queries to return the depth or level of a row within the hierarchy. While CONNECT BY is used to define the relationship between rows (parent-child), the LEVEL pseudo-column represents the position of each row in that hierarchy.

The LEVEL pseudo-column is automatically generated by Oracle when performing a CONNECT BY query. It helps to indicate how far a row is from the root of the hierarchy, with the root being at level 1 and subsequent levels incrementing as you move down the tree.

When used with CONNECT BY, LEVEL helps to display the hierarchical structure of the result set, allowing you to show the depth of each record within the hierarchy.

Syntax:

SELECT column_list, LEVEL

FROM table_name

START WITH condition

CONNECT BY PRIOR parent_column = child_column;

Key Components:

1. LEVEL: A special pseudo-column used in CONNECT BY queries to return the depth of each row in the hierarchy.
2. CONNECT BY: Defines the parent-child relationship between rows. It tells Oracle how to connect the rows and traverse through the hierarchy.
3. START WITH: Defines the starting point (root) of the hierarchy, where the query begins.
4. PRIOR: Used to reference the parent row’s value in the relationship defined by CONNECT BY.

 

Example 1: Basic CONNECT BY LEVEL Query

Consider an example table employees that has the columns emp_id, emp_name, and manager_id. This table represents a hierarchy of employees, where manager_id refers to the emp_id of the manager (self-referencing relationship).

SELECT emp_id, emp_name, manager_id, LEVEL

FROM employees

START WITH manager_id IS NULL

CONNECT BY PRIOR emp_id = manager_id;

Explanation:

• LEVEL: This pseudo-column shows the depth of each employee in the hierarchy. The root node (Alice) is at level 1, and employees reporting to her (Bob and Charlie) are at level 2, and so on.
• START WITH manager_id IS NULL: The query starts with the employee(s) whose manager_id is NULL, which is typically the root of the hierarchy.
• CONNECT BY PRIOR emp_id = manager_id: This defines the parent-child relationship by linking the emp_id of the parent (manager) to the manager_id of the child (employee).

Sample 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 of the Output:

• Alice (emp_id = 1) is the root node (level 1).
• Bob (emp_id = 2) and Charlie (emp_id = 3) report to Alice and are at level 2.
• David (emp_id = 4), Eve (emp_id = 5), and Frank (emp_id = 6) report to Bob and Charlie, and are at level 3.

 

Example 2: Using LEVEL to Limit Hierarchy Depth

You can limit the number of levels retrieved in the hierarchy by filtering the LEVEL pseudo-column.

SELECT emp_id, emp_name, LEVEL

FROM employees

START WITH manager_id IS NULL

CONNECT BY PRIOR emp_id = manager_id

AND LEVEL <= 2;

Explanation:

• AND LEVEL <= 2: This limits the output to only the first two levels of the hierarchy. The root (level 1) and its immediate children (level 2) are retrieved, but not the grandchildren (level 3 and beyond).

Sample Output:

emp_id	emp_name	LEVEL
1	Alice	1
2	Bob	2
3	Charlie	2

 

Example 3: Using LEVEL to Display Indentation (Visualization of Hierarchy)

You can use the LEVEL pseudo-column in combination with Oracle’s string functions to visually display the hierarchical structure, such as adding indentations based on the level.

SELECT LPAD(' ', LEVEL * 4) || emp_name AS indented_emp_name, LEVEL

FROM employees

START WITH manager_id IS NULL

CONNECT BY PRIOR emp_id = manager_id;

Explanation:

• LPAD(' ', LEVEL * 4): The LPAD function adds spaces to the employee’s name to create indentation, with each level indented by 4 spaces.

Sample Output:

indented_emp_name	LEVEL
Alice	1
Bob	2
Charlie	2
David	3
Eve	3
Frank	3

 

Example 4: Using LEVEL with ORDER BY to Sort Hierarchy

You can sort the rows based on the hierarchical level. The following query retrieves the employees in order of their level in the hierarchy.

SELECT emp_id, emp_name, LEVEL

FROM employees

START WITH manager_id IS NULL

CONNECT BY PRIOR emp_id = manager_id

ORDER BY LEVEL;

Explanation:

• ORDER BY LEVEL: This orders the result set by the LEVEL pseudo-column, ensuring that the rows are displayed in order of their depth in the hierarchy.

Sample Output:

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

 

Example 5: Using LEVEL to Avoid Cycles in Hierarchical Queries

To handle cases where there might be cycles in the data (e.g., an employee pointing to an ancestor as their manager), you can use the NOCYCLE option to avoid infinite loops.

SELECT emp_id, emp_name, LEVEL

FROM employees

START WITH manager_id IS NULL

CONNECT BY NOCYCLE PRIOR emp_id = manager_id;

Explanation:

• NOCYCLE: Prevents infinite recursion if a cycle exists in the hierarchical data. If the query detects a cycle (e.g., an employee points to a manager in the hierarchy who is also their ancestor), it stops processing and does not return those rows.

 

Practical Use Cases for LEVEL:

1. Tree Structure Representation: LEVEL can be used to display hierarchical data in a tree-like format, adding indentations or visual cues to show the relationship between rows.
2. Limiting Depth of Hierarchies: You can limit the query to a certain number of levels, which is useful when you're only interested in a specific level of a hierarchy (e.g., direct reports, first few generations).
3. Sorting Hierarchical Data: Using LEVEL to order rows in ascending or descending order of depth helps in understanding the hierarchy structure better.
4. Performance Optimization: By limiting the number of levels or using indexes on hierarchical columns, you can improve performance for deep hierarchies or large datasets.

 

Performance Considerations:

• Deep Hierarchies: Hierarchical queries with deep levels of recursion can be resource-intensive, especially if the data set is large. Use indexing on the parent-child columns to improve performance.
• Recursive Limit: By default, Oracle limits the recursion depth to 1000 levels. You can adjust this limit with MAXCONNECTBY if necessary:

·        ALTER SESSION SET MAXCONNECTBY = 2000;

• Efficient Queries: Filter unnecessary levels early in the query to avoid processing large parts of the hierarchy that you don’t need.

 

Conclusion:

The LEVEL pseudo-column is an important feature when working with hierarchical data in Oracle. It helps to display the depth of rows within a hierarchy and allows you to filter, sort, and visualize the hierarchical structure. By using CONNECT BY in conjunction with LEVEL, you can perform sophisticated tree-like queries to traverse parent-child relationships in your data.