CODEWITHSUNDEEP
sqloracle-database
kyooryu
kyooryu

Reputation: 1509

Why does oracle optimiser treat join by JOIN and WHERE differently?

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:

Execution plans

How is that possible? I always thought the optimiser treats JOIN exactly as the WHERE clause in the optimised query...

Upvotes: 5

Views: 1369

Answers (1)

davek
davek

Reputation: 22925

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.

Upvotes: 5

Related Questions