今天在ITPUB看到一个帖子,关于WHERE条件的执行顺序的。
http://www.itpub.net/thread-1755665-1-1.html
原文如下:
今天看到以下一句话,我记得以下的描述应该是针对RBO的吧,现在CBO优化器应该能自己判断了吧?
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,
那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
事实正好相反,可以过滤掉最大数量记录的条件必须写在WHERE子句的前面。
看下面一个经典例子:
SQL> desc x
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
X CHAR(1)
Y CHAR(1)
SQL> select * from x;
X Y
- -
a 1
b 2
1 3
2 4
SQL> select * from x where x=1 and y=3;
select * from x where x=1 and y=3
*
第 1 行出现错误:
ORA-01722: 无效数字
SQL> select * from x where y=3 and x=1;
X Y
- -
1 3
SQL> set autot traceonly exp
SQL> select * from x where x=1 and y=3;
执行计划
----------------------------------------------------------
Plan hash value: 2941724873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| X | 1 | 6 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("X")=1 AND TO_NUMBER("Y")=3)
SQL> select * from x where y=3 and x=1;
执行计划
----------------------------------------------------------
Plan hash value: 2941724873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| X | 1 | 6 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("Y")=3 AND TO_NUMBER("X")=1)
Note
-----
- dynamic sampling used for this statement
SQL>
带有复杂的条件的时候可能情况可能会更复杂。
阅读(1135) | 评论(2) | 转发(0) |