共计 4151 个字符，预计需要花费 11 分钟才能阅读完成。
这篇文章主要介绍了 Oracle 12CR2 中谓词推送怎么用，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。
在谓词推送中，优化器将包含在查询块中的相关谓词推送到视图查询块中。对于不能合并的视图，这种技术可以提高不能合并视图的执行计划。数据库可以使用推送谓词来访问索引或作为过滤。
例如，假设创建了一个 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 来进行过滤，也证明了可以将谓词推送到了视图中的查询块。
感谢你能够认真阅读完这篇文章，希望丸趣 TV 小编分享的“Oracle 12CR2 中谓词推送怎么用”这篇文章对大家有帮助，同时也希望大家多多支持丸趣 TV，关注丸趣 TV 行业资讯频道，更多相关知识等着你来学习!
正文完
