December 4, 2009
Some time ago the following question appeared in a forum:
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.
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).
It is important to keep in mind that the two solutions provided may or may not yield the same results. This will be evident in those cases where there is more than one row in REMINDER for a PROJ_ID WHERE REM_TYPE = ‘LAST’, and in cases where there is more than one row in NOTES for a PROJ_ID WHERE NOTE_TYPE = ‘QA’.
To work around the above issue, if it may occur, modify AlterEgo’s solution:
Can be modified as (assuming that PROJECTID is the primary key of the
SELECT COUNT(DISTINCT A.PROJECTID)
With the above change, you may find that AlterEgo’s SQL statement executes slightly faster than the solution that I provided (this may be Oracle version dependent).
One final note. When building SQL statements with aliases, it is easier to troubleshoot problems with the SQL statements if the alias name is somehow related to the object name that it represents:
Rather than using:
PROJECT A, REMINDER B, NOTES C
I would use:
PROJECT P, REMINDER R, NOTES N