通常情况下，我们在Oracle数据库中使用or作为条件过滤时，会出现查询转换，以提高语句执行效率。优化器将查询块转换为union all语句，当然，优化器会考虑哪个效率更高。 在以前的版本中，优化器使用CONCATENATION运算来执行or相关语句，从Oracle12.2开始，优化器使用union-all操作符。具有以下功能： - 支持各种转换之间的交互 - 避免共享查询结构 - 能够探索各种搜索策略 - 提供成本注释的重用 - 支持标准SQL语法 示例： ``` 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. ``` 第一个语句： ``` sql SELECT * FROM employees e, departments d WHERE (e.email='SSTILES' OR d.department_name='Treasury') AND e.department_id = d.department_id; ``` 第二个语句： ``` 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; ``` 如果不适用or扩展功能，优化器将e.email='SSTILES' OR d.department_name='Treasury'视为一个单元。因此优化器不能在上面使用索引，因此对他们进行全表扫描。 我们可以选择使用第二种语句方式，优化器将分别解析独立的谓词，提高查询效率。 在执行过程中，Oracle会自动转换成union-all方式。 下列输出为Oracle11g，oracle21c执行计划,可以看到，11g使用的是CONCATENATION方式。 ``` sql --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 | ```