[原创]oracle中采用自下而上的顺序解析where语句

2011-03-28

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

例如:

(低效)

SELECT … FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);

(高效)

SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’;

下面的语句是在实际工作中遇到的，where后面的语句位置随便放在执行时间上有很大的差别。

SELECT
 TRUNC(a.op_date,'dd')AS TODAYY,
 COUNT(DECODE(a.UP_CODE,'1','1')) AS UP_CODE_1,
 COUNT(DECODE(a.UP_CODE,'2','1')) AS UP_CODE_2,
 COUNT(DECODE(a.UP_CODE,'3','1')) AS UP_CODE_3,
 COUNT(UP_CODE),
 CONCAT(ROUND(COUNT(DECODE(a.UP_CODE,'1','1'))/COUNT(UP_CODE)*100,3),'%') AS PROPORTION
FROM ext.rfid_eseal  a
WHERE
a.op_date BETWEEN to_date('2010-09-08 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')AND to_date('2011-03-29 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')
AND a.up_wh_id=5 
AND op_type IS NOT NULL 
AND (a.on_off='0'OR a.on_off='1' OR a.on_off IS NULL) 
GROUP BY TRUNC(a.op_date,'dd')
ORDER BY TODAYY DESC;