Oracle 12CR2中谓词推送怎么用

这篇文章主要介绍了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_namefrom ( select employee_id, last_name, job_id, commission_pct, department_idfrom employeeswhere department_id=50unionselect employee_id, last_name, job_id, commission_pct, department_idfrom contract_workerswhere 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 = 50Plan 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$3Outline 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来进行过滤，也证明了可以将谓词推送到了视图中的查询块。

感谢你能够认真阅读完这篇文章，希望小编分享的"Oracle 12CR2中谓词推送怎么用"这篇文章对大家有帮助，同时也希望大家多多支持，关注行业资讯频道，更多相关知识等着你来学习!