Re: Query Optimization

On Feb 26, 3:53 pm, "Roy" <roy.b..._at_gmail.com> wrote:
> 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...

Give this re-write a try to see if it improves performance - I have essentially moved the exists statements into two inline views (some versions of Oracle may automatically perform such transformations): SELECT
  COUNT(A.PROJ_ID)
FROM
  PROJECT A,
  (SELECT DISTINCT
    B.PROJ_ID
  FROM
    REMINDER B
  WHERE
    B.REM_TYPE = 'LAST') B,
  (SELECT DISTINCT
    C.PROJ_ID
  FROM
    NOTES C
  WHERE
    C.NOTE_TYPE = 'QA') C
WHERE
  A.STAT_CD = 'CLOSED'
  AND A.PROJ_ID = B.PROJ_ID
  AND A.PROJ_ID = C.PROJ_ID; Very likely, you will need an index on the PROJ_ID column for each table, especially if the REMINDER and NOTES tables contain columns that are wide. An index on REMINDER.REM_TYPE might help. An index on NOTES.NOTE_TYPE also might help. Take a look at the explain plan (preferrably DBMS_XPLAN) for your query, and compare it to the explain plan for the one above. Also, make certain that the tables and indexes are analyzed (use DBMS_STATS for Oracle 8i and above).

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Feb 26 2007 - 18:31:52 CST