Oracle SQL: Performance implementing Recursive Queries in Version 19c
Could someone explain I'm performance testing and I'm stuck on something that should probably be simple..... I'm experiencing important performance degradation when executing a recursive Common Table Expression (CTE) in Oracle SQL 19c. The query is designed to traverse a hierarchical structure representing employee management. When I run the following query, it takes an unusually long time to complete: ```sql WITH RECURSIVE employee_hierarchy AS ( SELECT employee_id, manager_id, employee_name, 0 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1 FROM employees e INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id ) SELECT * FROM employee_hierarchy; ``` I've noticed that this query runs significantly slower on larger datasets, especially when the hierarchy depth exceeds five levels. I've already tried adding an index on `manager_id` to speed up the joins, but that didn’t yield the expected improvement. Additionally, I checked the execution plan, and it indicates that the recursive part of the query is consuming most of the resources. I also tried limiting the recursion depth using `OPTION (maxrecursion 100)` but it didn't help. Is there a best practice for optimizing recursive queries in Oracle SQL? Any insights into indexing strategies or query restructuring would be greatly appreciated. For context: I'm using Sql on Windows. This is happening in both development and production on Ubuntu 20.04. Any feedback is welcome! This is happening in both development and production on Debian. Is this even possible?