show parameter optimizer
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_mode string CHOOSE 如果此时收集表的统计信息，那么会影响执行计划的选择。
SQL> analyze table data_table compute statistics;
Table analyzed.
或者将session优化器模式改为CBO：
SQL> alter session set optimizer_mode='ALL_ROWS';
Session altered.
得到以下相同的结论。(原因：因为RBO下如果表存在统计信息，则会采用CBO)
实验语句3：
SQL> select id
from data_table
where data_type='zip'
and to_number(data) = 22
;
ID
----------
1234
这条语句执行explain plan for，
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("DATA_TABLE"."DATA_TYPE"='zip' AND
TO_NUMBER("DATA_TABLE"."DATA")=22) 会先使用data_type='zip'过滤，然后再执行to_number()。
实验语句4：
SQL> select id
from (select id, data
from data_table
where data_type='zip'
)
where to_number(data) = 22;
where to_number(data) = 22
*
ERROR at line 6:
ORA-01722: invalid number
但是现在第二条SQL执行报错。
这条语句执行explain plan for，发现谓词条件变为如下(顺序变了)：
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter(TO_NUMBER("DATA_TABLE"."DATA")=22 AND
"DATA_TABLE"."DATA_TYPE"='zip') CBO下或者收集表的统计信息后，都会先执行to_number()，再使用data_type='zip'，因此报错。
11.2.0.1.0
SQL> create table data_table
(id number
,data_type varchar(12)
,data varchar(30)
);
Table created.
SQL> insert into data_table values (1234, 'company','Pet Foods Inc');
1 row created.
SQL> insert into data_table values (1234, 'contact','Jennifer');
1 row created.
SQL> insert into data_table values (1234, 'zip','22');
1 row created.
SQL> insert into data_table values (1234, 'shipdate','03-OCT-2003');
1 row created.
SQL> commit;
Commit complete.
优化器模式是CBO：
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
实验语句5：
SQL> select id
from data_table
where data_type='zip'
and to_number(data) = 22;
ID
----------
1234
执行explain plan for，
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("DATA_TYPE"='zip' AND TO_NUMBER("DATA")=22)
实验语句6：
SQL> select id
from (select id, data
from data_table
where data_type='zip'
)
where to_number(data) = 22;
ID
----------
1234
执行explain plan for，
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("DATA_TYPE"='zip' AND TO_NUMBER("DATA")=22)
但当该表收集统计信息后，实验语句6就会报错：
SQL> select id
2 from (select id, data
3 from data_table
4 where data_type='zip'
5 )
6 where to_number(data) = 22;
where to_number(data) = 22
*
ERROR at line 6:
ORA-01722: invalid number
使用explain plan for后，
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter(TO_NUMBER("DATA")=22 AND "DATA_TYPE"='zip') 即使使用/*+ no_merge */也如此。
总结：1. 9i下，优化器默认模式是RBO，如果表没有统计信息，只会按照RBO方式，SQL查询语句按照谓词从右侧至左侧的顺序解析，如实验语句1。2. @dbsnake的书中曾介绍过，Oracle会内置一些查询转换规则，只要目标SQL满足了这些规则的要求，Oracle就会对其执行查询转换。Oracle 9i中查询转换是独立于优化器的，和优化器类型无关，因为Oracle此时认为经过查询转换后的等价改写SQL的执行效率一定比原目标SQL的执行效率高。我猜9i内置