在谓词推送中，优化器将相关谓词从外边查询块“推送”到视图查询块中。尤其对于未合并的视图，次技术改进了未合并视图的子计划。数据库可以使用推入谓词来访问所以或用作过滤器。

示例：使用如下方式创建表

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);

创建一个视图

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> 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> 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查询转换之谓词推送