I have the following tables:
Project Unit ------------- --------------- ID | IDUnit ID | IdParent
I have to select all projects that have the IDUnit equal to
X and all its descendants.
I am doing the following:
SELECT p.* FROM Project p WHERE ( P_Id IS NULL OR ( p.IDUnit IN (SELECT ID FROM Unit START WITH ID = P_Id CONNECT BY PRIOR ID = IdParent) ) )
It works, but the table
Unit has too many rows, and the query takes over 5 seconds to complete when there is value on
How can I optimize this query?