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:

FROM Project p

  P_Id    IS NULL

  OR (
    p.IDUnit IN (SELECT ID FROM Unit
    START WITH ID =   P_Id

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?

  • Have you had any joy with this? Apr 27, 2012 at 14:17

1 Answer 1


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
  • Eliminating OR conditions often helps, but in this case I'm not sure it will. Without a comment from the OP we won't know. Just to be picky I could also point out that this solution would need to be modified to return the same columns as the OP's. Apr 27, 2012 at 14:04
  • Yes, I don't know if it would help either. Isn't p.* the same as id, idunit? Apr 27, 2012 at 14:12
  • I assumed he wasn't showing all the columns. You may be right. Apr 27, 2012 at 14:15
  • 1
    I see what you mean - I guess he isn't but I suppose it is still ok to assume that he is in the context of the information given and let the OP translate back to his real-world case Apr 27, 2012 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.