数据库版本11g
select * From emp where ename='SMITH' or empno=7902
SQL> select * from table(dbms_xplan.display_cursor('5gj2f75x9xtnx'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5gj2f75x9xtnx, child number 0
-------------------------------------
select * From emp where ename='SMITH' or empno=7902
Plan hash value: 962431618
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 2 | 78 | 8
| 2 | BITMAP CONVERSION TO ROWIDS | | | |
| 3 | BITMAP OR | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | |
|* 5 | INDEX RANGE SCAN | I_EMP | | | 1
| 6 | BITMAP CONVERSION FROM ROWIDS| | | |
|* 7 | INDEX RANGE SCAN | SYS_C00182518 | | | 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
5 - access("ENAME"='SMITH')
7 - access("EMPNO"=7902)
select /*+ USE_CONCAT */* From emp where ename='SMITH' or empno=7902
SQL> select * from table(dbms_xplan.display_cursor('61agkrn33a0z1'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 61agkrn33a0z1, child number 0
-------------------------------------
select /*+ USE_CONCAT */* From emp where ename='SMITH' or empno=7902
Plan hash value: 3488550714
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (10
| 1 | CONCATENATION | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 39 | 1 (
|* 3 | INDEX UNIQUE SCAN | SYS_C00182518 | 1 | | 0 (
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 39 | 2 (
|* 5 | INDEX RANGE SCAN | I_EMP | 1 | | 1 (
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
3 - access("EMPNO"=7902)
4 - filter(LNNVL("EMPNO"=7902))
5 - access("ENAME"='SMITH')
24 rows selected
对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.