这篇文章主要介绍了Oracle 12CR2中谓词推送怎么用，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

在谓词推送中，优化器将包含在查询块中的相关谓词推送到视图查询块中。对于不能合并的视图，这种技术可以提高不能合并视图的执行计划。数据库可以使用推送谓词来访问索引或作为过滤。
例如，假设创建了一个hr.contract_workers表:

SQL>droptablecontract_workers;Tabledropped.SQL>createtablecontract_workersas(select*fromemployeeswhere1=2);Tablecreated.SQL>insertintocontract_workersvalues(306,'bill','jones','bjones','555.555.2000','07-jun-02','ac_account',8300,0,205,110);1rowcreated.SQL>insertintocontract_workersvalues(406,'jill','ashworth','jashworth','555.999.8181','09-jun-05','ac_account',8300,0,205,50);1rowcreated.SQL>insertintocontract_workersvalues(506,'marcie','lunsford','mlunsford','555.888.2233','22-jul-01','ac_account',8300,0,205,110);1rowcreated.SQL>commit;Commitcomplete.SQL>createindexcontract_workers_indexoncontract_workers(department_id);Indexcreated.

创建一个视图引用employees与contract_workers表。视图使用了union集合操作:

SQL>createviewall_employees_vwas2selectemployee_id,last_name,job_id,commission_pct,department_id3fromemployees4union5selectemployee_id,last_name,job_id,commission_pct,department_id6fromcontract_workers;Viewcreated.

然后对视图执行查询:

selectlast_namefromall_employees_vwwheredepartment_id=50;

因为视图是一个union集合操作查询，优化器不能合并视图的查询到主查询块。优化器可以通过推送谓词来转换查询，where子句条件department_id=50，会推送到视图的union集合操作查询中，转换后的等价查询如下:

selectlast_namefrom(selectemployee_id,last_name,job_id,commission_pct,department_idfromemployeeswheredepartment_id=50unionselectemployee_id,last_name,job_id,commission_pct,department_idfromcontract_workerswheredepartment_id=50);

转换后的查询现在可以考虑对每个查询块使用索引或全表扫描,查询视图语句的执行计划如下:

SQL>select*fromtable(dbms_xplan.display_cursor(null,null,'advancedallstatslastrunstats_lastpeeked_binds'));SQL_ID265ccrp674n30,childnumber0-------------------------------------selectlast_namefromall_employees_vwwheredepartment_id=50Planhashvalue: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|SELECTSTATEMENT||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|SORTUNIQUE||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|TABLEACCESSFULL|EMPLOYEES|1|100K|2540K||273(1)|00:00:01|100K|00:00:00.41|948|942|||||*5|TABLEACCESSFULL|CONTRACT_WORKERS|1|1|60||2(0)|00:00:01|1|00:00:00.01|7|0||||-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------QueryBlockName/ObjectAlias(identifiedbyoperationid):-------------------------------------------------------------1-SET$1/ALL_EMPLOYEES_VW@SEL$12-SET$14-SEL$2/EMPLOYEES@SEL$25-SEL$3/CONTRACT_WORKERS@SEL$3OutlineData-------------/*+BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE('12.2.0.1')DB_VERSION('12.2.0.1')ALL_ROWSNO_PARALLELOUTLINE_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*/PredicateInformation(identifiedbyoperationid):---------------------------------------------------4-filter("DEPARTMENT_ID"=50)5-filter("DEPARTMENT_ID"=50)

从执行计划的Predicate Information部分可以看到4,5操作使用了department_id=50来分别对表employees和contract_workers来进行过滤，也证明了可以将谓词推送到了视图中的查询块。

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