Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Have you had any joy with this? –  Jack Douglas Apr 27 '12 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
share|improve this answer
    
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. –  Leigh Riffel Apr 27 '12 at 14:04
    
Yes, I don't know if it would help either. Isn't p.* the same as id, idunit? –  Jack Douglas Apr 27 '12 at 14:12
    
I assumed he wasn't showing all the columns. You may be right. –  Leigh Riffel Apr 27 '12 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 –  Jack Douglas Apr 27 '12 at 14:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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