通常情况下，我们在Oracle数据库中使用or作为条件过滤时，会出现查询转换，以提高语句执行效率。优化器将查询块转换为union all语句，当然，优化器会考虑哪个效率更高。
在以前的版本中，优化器使用CONCATENATION运算来执行or相关语句，从Oracle12.2开始，优化器使用union-all操作符。具有以下功能：
- 支持各种转换之间的交互
- 避免共享查询结构
- 能够探索各种搜索策略
- 提供成本注释的重用
- 支持标准SQL语法
示例：
--环境准备
SQL> ALTER TABLE hr.departments ADD CONSTRAINT department_name_uk UNIQUE (department_name);
DELETE FROM hr.employees WHERE employee_id > 999;
DECLARE
v_counter NUMBER(7) := 1000;
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO hr.employees
VALUES (v_counter,null,'Doe','Doe' || v_counter || '@example.com',null,'07-JUN-02','AC_ACCOUNT',null,null,null,50);
v_counter := v_counter + 1;
END LOOP;
END;
/ALTER TABLE hr.departments ADD CONSTRAINT department_name_uk UNIQUE (department_name)
*
ERROR at line 1:
ORA-02261: such unique or primary key already exists in the table
SQL>
0 rows deleted.
SQL> 2 3 4 5 6 7 8 9 10
PL/SQL procedure successfully completed.
SQL> COMMIT;
Commit complete.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'hr', tabname => 'employees');
PL/SQL procedure successfully completed.
第一个语句：
SELECT *
FROM employees e, departments d
WHERE (e.email='SSTILES' OR d.department_name='Treasury')
AND e.department_id = d.department_id;
第二个语句：
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;
如果不适用or扩展功能，优化器将e.email=’SSTILES’ OR d.department_name=’Treasury’视为一个单元。因此优化器不能在上面使用索引，因此对他们进行全表扫描。
我们可以选择使用第二种语句方式，优化器将分别解析独立的谓词，提高查询效率。 在执行过程中，Oracle会自动转换成union-all方式。
下列输出为Oracle11g，oracle21c执行计划,可以看到，11g使用的是CONCATENATION方式。
--11g
Plan hash value: 1571924483
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 105 (100)| |
| 1 | CONCATENATION | | | | | |
| 2 | NESTED LOOPS | | 9101 | 693K| 101 (0)| 00:00:02 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 21 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DEPARTMENT_NAME_UK | 1 | | 0 (0)| |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 9101 | 506K| 100 (0)| 00:00:02 |
|* 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 9101 | | 21 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 78 | 4 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 57 | 3 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | EMP_EMAIL_UK | 1 | | 2 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 21 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |
----------------------------------------------------------------------------------------------------
--21c
Plan hash value: 1161149427
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 105 (100)| |
| 1 | UNION-ALL | | | | | |
| 2 | NESTED LOOPS | | 1 | 78 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 57 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | EMP_EMAIL_UK | 1 | | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 21 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |
| 7 | NESTED LOOPS | | 9101 | 693K| 101 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 21 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | DEPARTMENT_NAME_UK | 1 | | 0 (0)| |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 9101 | 506K| 100 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 9101 | | 21 (0)| 00:00:01 |