Why does oracle optimiser treat join by JOIN and WHERE differently?
oracle, sql
Solution
From here:
In general you should find that the cost of a table scan will increase when you enable CPU Costing (also known as "System Statistics"). This means that your improved run time is likely to be due to changes in execution path that have started to favour execution plans. There are a few articles about system statistics on my blog that might give you more background, and a couple of links from there to other relevant articles: http://jonathanlewis.wordpress.com/category/oracle/statistics/system-stats/
In other words, your statistics might be stale, but since you have "turned them off" for this query, you avoid using an inefficient path: hence the (temporary?) improvement.
Problem
I have a query on which I used query optimiser: ``` SELECT res.studentid, res.examid, r.percentcorrect, MAX(attempt) AS attempt FROM tbl res JOIN (SELECT studentid, examid, MAX(percentcorrect) AS percentcorrect FROM tbl GROUP BY studentid, examid) r ON r.studentid = res.studentid AND r.examid = res.examid AND r.percentcorrect = res.percentcorrect GROUP BY res.studentid, res.examid, r.percentcorrect ORDER BY res.examid ``` What surprised me was that the optimiser returned the following as over 40% faster: ``` SELECT /*+ NO_CPU_COSTING */ res.studentid, res.examid, r.percentcorrect, MAX(attempt) AS attempt FROM tbl res, (SELECT studentid, examid, MAX(percentcorrect) AS percentcorrect FROM tbl GROUP BY studentid, examid) r WHERE r.studentid = res.studentid AND r.examid = res.examid AND r.percentcorrect = res.percentcorrect GROUP BY res.studentid, res.examid, r.percentcorrect ORDER BY res.examid ``` Here are the execution plans for both: How is that possible? I always thought the optimiser treats JOIN exactly as the WHERE clause in the optimised query...