Another dig at 10g Sql Advisory

First time I sat through a 10g demo, I was led to believe that this is it. No more need for Oracle practitioners to tune their sql, the new Sql Advisory feature will do this. Well I have written about a number of cases where the advisory just doesnt have a clue, here is yet another example of when only a clever human being can help.

I have a large update, the outer loop I select is from the cursor below:

CURSOR C1
is
SELECT f.casenumberid,MAX(f.caseversionnumber)
FROM f_safetyreport F
GROUP BY F.CASENUMBERID;


Now for the update I need to do, I a human being can see that I will not need to select the records which have max(f.caseversionnumber) of 1

so a re-write:
CURSOR C1
IS
SELECT f.casenumberid,MAX(f.caseversionnumber)
FROM f_safetyreport F
HAVING MAX(f.caseversionnumber) > 1
GROUP BY F.CASENUMBERID;

but the GROUP BY can slow things down, so only I a human being who understands the application is aware of a table which records the max(caseversionnumber) against a casenumberid, so here is a further rewrite which really spped things up:

CURSOR C1
IS
SELECT h.casenumberid,h.casemaxversion
FROM h_caseversions H
WHERE h.casemaxversion > 1;

There you go, clever human beings are still needed :)