通常情况下，我们在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 |
```

ITPub博客

【SQL】Oracle查询转换之 OR用法

最新文章