How to search for an ID and its descendants in this query

optimizationoracleperformancequeryquery-performance

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 P_Id.

How can I optimize this query?

Best Answer

How about something like this:

select id, idunit
from (select id as idunit from unit start with id=p_id connect by prior id = idparent)
     join project using(idunit)
where p_id is not null
union all
select id, idunit from project where p_id is null
Related Question