Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Query Optimization
I am learning SQL (trial by fire) and have a question regarding a
query optimization. Lets say I have three tables 'project', 'notes' &
'reminder'. Key on 'project' is 'proj_id'. The other two tables
reference this key as 'notes' contains note entries on a given project
and 'reminder' tracks todo type and due dates.
select count(a.proj_id)
from project a
where a.stat_cd = 'CLOSED'
and exists
(
select b.proj_id from reminder b where b.rem_type = 'LAST' and a.proj_id = b.proj_id ) and exists
(
select c.proj_id from notes c where c.note_type = 'QA' and a.proj_id = c.proj_id )
I am trying to determine the number of projects that have a 'CLOSED' status, contain a reminder type of 'LAST' and has a note type of 'QA'
I get the result I am looking for but these are very large tables and it takes over 30 minutes for this to run. Is there a better way to write this query? I'm betting there is. Thank you in advance for your advice.
Regards... Received on Mon Feb 26 2007 - 14:53:35 CST