Oracle优化器会根据成本考虑是否进行查询转换，例如考虑是否将谓词的or条件转换为union all，即，执行or expansion。例如：
原始SQL:
SELECT *
FROM employees e, departments d
WHERE (e.email = 'SSTILES' OR d.department_name = 'Treasury')
AND e.department_id = d.department_id;
优化器可能会将上面的原始SQL改写成如下SQL。
SELECT *
FROM employees e, departments d
WHERE e.email = 'SSTILES'
AND e.department_id = d.department_id
UNION ALL
SELECT *
FROM employees e, departments d
WHERE d.department_name = 'Treasury'
AND e.department_id = d.department_id;
过程如下：
Oracle版本：
SQL> select banner_full from v$version;
查看_no_or_expansion参数，默认关闭，即默认启用了or expansion
SQL> conn as sysdba
SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '_no_or_expansion';
执行SQL查看执行计划
SQL> conn hr/hr@cjcpdb
SQL> set autotrace on
SQL>
SELECT *
FROM employees e, departments d
WHERE (e.email = 'SSTILES' OR d.department_name = 'Treasury')
AND e.department_id = d.department_id;
实际上SQL执行了or expansion，自动将or转换成union all，例如：
SELECT *
FROM employees e, departments d
WHERE e.email = 'SSTILES'
AND e.department_id = d.department_id
UNION ALL
SELECT *
FROM employees e, departments d
WHERE d.department_name = 'Treasury'
AND e.department_id = d.department_id;
通过10053事件，看下实际情况是否真是将or改写成union all
SQL> conn hr/hr@cjcpdb
SQL> alter session set tracefile_identifier='10053';
SQL> SELECT *12345*/ *
FROM employees e, departments d
WHERE (e.email = 'SSTILES' OR d.department_name = 'Treasury')
AND e.department_id = d.department_id;
SQL> alter session set events '10053 trace name context forever ,level 1';
[root@cjcos01 trace]# pwd
/u01/app/oracle/diag/rdbms/cjcdb/cjcdb/trace
[root@cjcos01 trace]# ls *10053*
cjcdb_ora_22978_10053.trc cjcdb_ora_22978_10053.trm
如果trace下没有生成10053事件的日志，可能是因为这个sql语句已经解析过，可以改写下SQL文本，例如添加/*12345*/等。
[root@cjcos01 trace]# vim cjcdb_ora_22978_10053.trc
当前SQL
搜索关键字：Final query after transformations，查看SQL改写情况
通过10053trace可以看到，数据库确实是将or条件改写成了union all，并添加了别名VW_ORE_*,这也就是为什么第一个执行计划里有VW_ORE_*的原因。
查看禁用or expansion后的执行计划
SQL> alter session set "_no_or_expansion"=true;
SELECT *
FROM employees e, departments d
WHERE (e.email = 'SSTILES' OR d.department_name = 'Treasury')
AND e.department_id = d.department_id;
更多数据库相关学习资料，可以查看我的ITPUB博客，网名chenoracle：
http://blog.itpub.net/29785807/
