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

【SQL】Oracle查詢轉換之 OR用法

相關文章