在謂詞推送中，最佳化器將相關謂詞從外邊查詢塊“推送”到檢視查詢塊中。尤其對於未合併的檢視，次技術改進了未合併檢視的子計劃。資料庫可以使用推入謂詞來訪問所以或用作過濾器。

示例： 使用如下方式建立表
``` sql
DROP TABLE contract_workers;
CREATE TABLE contract_workers AS (SELECT * FROM employees where 1=2);
INSERT INTO contract_workers VALUES (306, 'Bill', 'Jones', 'BJONES',
  '555.555.2000', '07-JUN-02', 'AC_ACCOUNT', 8300, 0,205, 110);
INSERT INTO contract_workers VALUES (406, 'Jill', 'Ashworth', 'JASHWORTH', 
  '555.999.8181', '09-JUN-05', 'AC_ACCOUNT', 8300, 0,205, 50);
INSERT INTO contract_workers VALUES (506, 'Marcie', 'Lunsford', 
  'MLUNSFORD', '555.888.2233', '22-JUL-01', 'AC_ACCOUNT', 8300, 
  0, 205, 110);
COMMIT;
CREATE INDEX contract_workers_index ON contract_workers(department_id);
```

建立一個檢視
``` sql
CREATE VIEW all_employees_vw AS
  ( SELECT employee_id, last_name, job_id, commission_pct, department_id
    FROM   employees )
  UNION
  ( SELECT employee_id, last_name, job_id, commission_pct, department_id
    FROM   contract_workers );
```

執行查詢,並檢視執行計劃
``` sql
SQL> set lines 2000
SQL> set pages 999
SQL> explain plan for SELECT last_name
FROM   all_employees_vw
WHERE  department_id = 50;
  2    3  
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3783741883

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation		       | Name			| Rows	| Bytes |TempSpc| Cost (%CPU)| Time	|
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |			|  9102 |   239K|	|   175   (2)| 00:00:03 |
|   1 |  VIEW			       | ALL_EMPLOYEES_VW	|  9102 |   239K|	|   175   (2)| 00:00:03 |
|   2 |   SORT UNIQUE		       |			|  9102 |   231K|   368K|   175   (2)| 00:00:03 |
|   3 |    UNION-ALL		       |			|	|	|	|	     |		|
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES		|  9101 |   231K|	|   101   (0)| 00:00:02 |
|*  5 |      INDEX RANGE SCAN	       | EMP_DEPARTMENT_IX	|  9101 |	|	|    22   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| CONTRACT_WORKERS	|     1 |    60 |	|     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN	       | CONTRACT_WORKERS_INDEX |     1 |	|	|     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("DEPARTMENT_ID"=50)
   7 - access("DEPARTMENT_ID"=50)

Note
-----
   - dynamic sampling used for this statement (level=2)

24 rows selected.
```

因為上述檢視是一個union集合查詢， 最佳化器不能將檢視的查詢合併到訪問查詢塊中。 但是，最佳化器可以透過將其謂詞、where子句的條件department_id=50推送到檢視的union集合查詢中來轉換訪問語句。等效轉換查詢如下：
``` sql
SQL> explain plan for SELECT last_name
FROM   ( SELECT employee_id, last_name, job_id, commission_pct, department_id
         FROM   employees
         WHERE  department_id=50
         UNION
         SELECT employee_id, last_name, job_id, commission_pct, department_id
           2    3    4    5    6    7  FROM   contract_workers
         WHERE  department_id=50 );  8

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3462933984

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation		       | Name			| Rows	| Bytes |TempSpc| Cost (%CPU)| Time	|
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |			|  9102 |   124K|	|   175   (2)| 00:00:03 |
|   1 |  VIEW			       |			|  9102 |   124K|	|   175   (2)| 00:00:03 |
|   2 |   SORT UNIQUE		       |			|  9102 |   231K|   368K|   175   (2)| 00:00:03 |
|   3 |    UNION-ALL		       |			|	|	|	|	     |		|
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES		|  9101 |   231K|	|   101   (0)| 00:00:02 |
|*  5 |      INDEX RANGE SCAN	       | EMP_DEPARTMENT_IX	|  9101 |	|	|    22   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| CONTRACT_WORKERS	|     1 |    60 |	|     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN	       | CONTRACT_WORKERS_INDEX |     1 |	|	|     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("DEPARTMENT_ID"=50)
   7 - access("DEPARTMENT_ID"=50)

Note
-----
   - dynamic sampling used for this statement (level=2)

24 rows selected.
```

透過上述兩個執行計劃可以看出，Oracle最佳化器比較強大，會自動轉換成效率更高的查詢語句，提高資料庫效率。

【SQL】Oracle查詢轉換之謂詞推送

相關文章