ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾. 例如: (低效,执行时间156.3秒) SELECT ... FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER' AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO); (高效,执行时间10.6秒) SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER';
btw:楼主的语句好奇怪,要查什么?
就是说t1.a=变量a检索到一仆结果集
再进行t1.a = t2.b的连结
应该是这样的吧?
例如:
(低效,执行时间156.3秒)
SELECT ... FROM EMP E
WHERE SAL > 50000 AND JOB = ‘MANAGER' AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO); (高效,执行时间10.6秒)
SELECT … FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER';
你将两个条件都故意写错,然后看那个地方先报错,就是先执行那个条件了。
如where t1.aaa = t2.b and t1.aaaa=变量a