Love to Performance : CONNECT BY PRIOR in Oracle Performance Issue

CONNECT BY PRIOR clause is Oracle provided a great solution for hierarchical queries but connect by prior in Oracle performance issue are headache. It’s because of large dataset misuse of keywords, less focus, or keywords not in the correct order. And there are many causes for connecting prior in Oracle performance.

Now I explore some common performance issues associated with CONNECT BY PRIOR and guide to mitigate them:

Performance Issues with CONNECT BY PRIOR in Oracle:

1. Full Table Scans Issue:

--
SELECT * FROM employees 
CONNECT BY PRIOR employee_id = manager_id;
--

 If the table does not create an index for PRIOR related column, then there can be performance issues. It’s because if there are millions of records in the table every row tries to map with all the rows according to PRIOR condition.

Think about if you want to create a hierarchy on multiple levels using part numbers there can be a huge performance drop due to a full table scan multiple times.

Create an index on the columns for PRIOR.

-- Create an Index 
CREATE INDEX emp_manager_idx ON employee_table(manager_id);
--

2. Excessive Recursion Issue:

If there is a hierarchy it has lots of levels then Recursively calls until finds the leaf node. However, data may not be useful until the leaf node.

-- Problematic Query
SELECT * FROM employees 
CONNECT BY PRIOR employee_id = manager_id 
START WITH employee_id = 100;
--

 The basic solution for this is to Limit the depth of recursion using the LEVEL keyword.

-- Limit the Depth
SELECT * FROM employees 
WHERE LEVEL <= 3 CONNECT BY PRIOR employee_id = manager_id 
START WITH employee_id = 100;
--

3. Apply filtering conditions as early

If you looking for CONNECT BY PRIOR queries with complex conditions those conditions apply as early as possible in the query. Then CONNECT BY PRIOR apply only for fewer records in the table.

-- Problematic Query
SELECT * FROM employees 
CONNECT BY PRIOR employee_id = manager_id AND salary > 50000 
START WITH employee_id = 100; 
--

apply filtering conditions as early as possible. Then you get fewer records that will improve the performance for complex quarries.

-- Better Structured Query
SELECT * FROM employees 
WHERE salary > 50000 
CONNECT BY PRIOR employee_id = manager_id 
START WITH employee_id = 100;
--

Tips for Mitigating Performance Issues:

1. Indexing:

• Add index into Connect by and START WITH clause and if there is a where condition then if required add index there as well.

--
CREATE INDEX emp_hierarchy_idx ON employee_table(manager_id);
--

2. Limit the Depth:

The LEVEL keyword is used to set the number of levels in the tree hierarchy. If you are not required all the levels use the LEVEL keyword to limit it.

--
SELECT * FROM employee_table 
WHERE LEVEL <= 3 
CONNECT BY PRIOR employee_id = manager_id;
--

3. Filter Early:

• Condition in the where Clouse triggers as early as possible. That enables to restriction of the number of records that affect CONNECT BY Clouse. If there is a specific ROOT you can set the ROOT node by START WITH Clouse.

--
SELECT * FROM employees 
WHERE department = 'Sales' 
CONNECT BY PRIOR employee_id = manager_id 
START WITH employee_id = 100;
--

4. Materialized Views:

If you have static data in your table this is a good solution for CONNECT BY performance. Data is already precomputed and that time is saved if you can use Materialized Views for queries.

--
CREATE MATERIALIZED VIEW emp_hierarchy AS SELECT * 
FROM employees 
WHERE LEVEL <= 3 
CONNECT BY PRIOR employee_id = manager_id 
START WITH employee_id = 100;
--

5. Add inner query

The below examples show how to add an inner query if required. This is to filter out the CONNECT BY dataset. Query explicitly reduces the set of data set who work in Sales, which Oracle can then do before performing the CONNECT BY.

SELECT * FROM (
               SELECT * FROM employees 
                WHERE dept_id = 'SALE'
              )
START WITH manager_id is null
CONNECT BY PRIOR employee_id = manager_id

CONNECT BY and START WITH are doing related to hierarchical things. Therefore writing queries efficiently is good for performance. Otherwise, there can be a CONNECT BY prior in Oracle performance issue when data load increases.