Oracle 12CR2查詢轉換之謂詞推送

在謂詞推送中，最佳化器將包含在查詢塊中的相關謂詞推送到檢視查詢塊中。對於不能合併的檢視，這種技術可以提高不能合併檢視的執行計劃。資料庫可以使用推送謂詞來訪問索引或作為過濾。
例如，假設建立了一個hr.contract_workers表:

SQL> drop table contract_workers;
Table dropped.
SQL> create table contract_workers as (select * from employees where 1=2);
Table created.
SQL> insert into contract_workers values (306, 'bill', 'jones', 'bjones','555.555.2000', '07-jun-02', 'ac_account', 8300, 0,205, 110);
1 row created.
SQL> insert into contract_workers values (406, 'jill', 'ashworth', 'jashworth','555.999.8181', '09-jun-05', 'ac_account', 8300, 0,205, 50);
1 row created.
SQL> insert into contract_workers values (506, 'marcie', 'lunsford', 'mlunsford','555.888.2233', '22-jul-01', 'ac_account', 8300, 0,205, 110);
1 row created.
SQL> commit;
Commit complete.
SQL> create index contract_workers_index on contract_workers(department_id);
Index created.

建立一個檢視引用employees與contract_workers表。檢視使用了union集合操作:

SQL> create view all_employees_vw as
  2  select employee_id, last_name, job_id, commission_pct, department_id
  3  from employees 
  4  union
  5  select employee_id, last_name, job_id, commission_pct, department_id
  6  from contract_workers;
View created.

然後對檢視執行查詢:

select last_name from all_employees_vw where department_id = 50;

因為檢視是一個union集合操作查詢，最佳化器不能合併檢視的查詢到主查詢塊。最佳化器可以透過推送謂詞來轉換查詢，where子句條件department_id=50，會推送到檢視的union集合操作查詢中，轉換後的等價查詢如下:

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
from contract_workers
where department_id=50 );

轉換後的查詢現在可以考慮對每個查詢塊使用索引或全表掃描,查詢檢視語句的執行計劃如下:

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  265ccrp674n30, child number 0
-------------------------------------
select last_name from all_employees_vw where department_id = 50
Plan hash value: 1422200799
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |      1 |        |       |       |  1018 (100)|          |    100K|00:00:01.37 |     955 |    942 |       |       |          |
|   1 |  VIEW                | ALL_EMPLOYEES_VW |      1 |    100K|  2637K|       |  1018   (1)| 00:00:01 |    100K|00:00:01.37 |     955 |    942 |       |       |          |
|   2 |   SORT UNIQUE        |                  |      1 |    100K|  2540K|  3936K|  1018   (1)| 00:00:01 |    100K|00:00:01.18 |     955 |    942 |  8416K|  1135K| 7480K (0)|
|   3 |    UNION-ALL         |                  |      1 |        |       |       |            |          |    100K|00:00:00.76 |     955 |    942 |       |       |          |
|*  4 |     TABLE ACCESS FULL| EMPLOYEES        |      1 |    100K|  2540K|       |   273   (1)| 00:00:01 |    100K|00:00:00.41 |     948 |    942 |       |       |          |
|*  5 |     TABLE ACCESS FULL| CONTRACT_WORKERS |      1 |      1 |    60 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |      0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SET$1 / ALL_EMPLOYEES_VW@SEL$1
   2 - SET$1
   4 - SEL$2 / EMPLOYEES@SEL$2
   5 - SEL$3 / CONTRACT_WORKERS@SEL$3
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "ALL_EMPLOYEES_VW"@"SEL$1")
      FULL(@"SEL$3" "CONTRACT_WORKERS"@"SEL$3")
      FULL(@"SEL$2" "EMPLOYEES"@"SEL$2")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("DEPARTMENT_ID"=50)
   5 - filter("DEPARTMENT_ID"=50)

從執行計劃的Predicate Information部分可以看到4,5操作使用了department_id=50來分別對錶employees和contract_workers來進行過濾，也證明了可以將謂詞推送到了檢視中的查詢塊。