转自:http://www.cnblogs.com/yuanjw/archive/2010/03/04/1678093.html
先看下面的一小段代码
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> 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’;
举例说明:
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> UPDATE XCSV_TEXT A SET ID05 = (
SELECT ( COUNT ( DISTINCT ID05) + 1 )
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> UPDATE XCSV_TEXT A SET ID05 = (
SELECT ( COUNT ( DISTINCT ID05) + 1 )
FROM
XCSV_TEXT B
WHERE
B.ID04
IN
(
'
MD
'
,
'
RT
'
)
AND
TO_NUMBER(B.ID05)
<
TO_NUMBER(A.ID05)
)
WHERE A.ID04 IN ( ' MD ' , ' RT ' );
)
WHERE A.ID04 IN ( ' MD ' , ' RT ' );
如果用这句代码来操作上面表中的数据,那么将会报错.
根据Oracle中Where条件的执行顺序,将会先执行TO_NUMBER(B.ID05) < TO_NUMBER(A.ID05)这一段, “Ver000017b65b65242dde1a9a014ac0e525ffb60”是不能To_Number的.
而改成
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> UPDATE XCSV_TEXT A SET ID05 = (
SELECT ( COUNT ( DISTINCT ID05) + 1 )
FROM XCSV_TEXT B
WHERE
TO_NUMBER(B.ID05) < TO_NUMBER(A.ID05)
AND B.ID04 IN ( ' MD ' , ' RT ' )
)
WHERE A.ID04 IN ( ' MD ' , ' RT ' );
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> UPDATE XCSV_TEXT A SET ID05 = (
SELECT ( COUNT ( DISTINCT ID05) + 1 )
FROM XCSV_TEXT B
WHERE
TO_NUMBER(B.ID05) < TO_NUMBER(A.ID05)
AND B.ID04 IN ( ' MD ' , ' RT ' )
)
WHERE A.ID04 IN ( ' MD ' , ' RT ' );
就正常了,而且提高了执行效率(至于为什么提高了执行效率,不做讨论.).
结论:
ORACLE采用自下而上的顺序解析WHERE子句,编写Where条件需要注意顺序,为了避免不必要的错误,也为了提高执行效率!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22980662/viewspace-708871/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22980662/viewspace-708871/