在谓词推送中，优化器将相关谓词从外边查询块“推送”到视图查询块中。尤其对于未合并的视图，次技术改进了未合并视图的子计划。数据库可以使用推入谓词来访问所以或用作过滤器。 示例： 使用如下方式创建表 ``` 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优化器比较强大，会自动转换成效率更高的查询语句，提高数据库效率。