sql - 为什么 oracle 优化器对 join by JOIN 和 WHERE 的处理方式不同？

Question

我有一个使用查询优化器的查询:

  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

令我惊讶的是，优化器返回以下结果的速度提高了 40% 以上:

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

以下是两者的执行计划:



这怎么可能？我一直认为优化器将 JOIN 视为优化查询中的 WHERE 子句......

Answer 1

来自 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/

换句话说，您的统计数据可能是陈旧的，但由于您已为此查询“关闭了它们”，因此您避免使用低效路径:因此(临时？)改进。