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:
- 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); --
- 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
-- Limit the Depth SELECT * FROM employees WHERE LEVEL <= 3 CONNECT BY PRIOR employee_id = manager_id START WITH employee_id = 100; --
- 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:
- 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); --
- 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; --
- 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; --
- 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; --
- 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.