Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Query Optimization

Query Optimization

From: Roy <roy.ball_at_gmail.com>
Date: 26 Feb 2007 12:53:35 -0800
Message-ID: <1172523215.449990.175370@v33g2000cwv.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US