/*+ USE_CONCAT */ 的用法

数据库版本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的组合查询.