Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use predicate push in Oracle 12CR2, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.

In predicate push, the optimizer pushes related predicates contained in the query block into the view query block. For views that cannot be merged, this technique can improve the execution plan of non-merged views. The database can use push predicates to access the index or as a filter.

For example, suppose you create a hr.contract_ works table:

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, 'ac_account', 8300, 0Magi 205,110) 1 row created.SQL > insert into contract_workers values; 1 row created.SQL > insert into contract_workers values; 1 row created.SQL > insert into contract_workers values; 1 row created.SQL > insert into contract_workers values; 1 row created.SQL > commit Commit complete.SQL > create index contract_workers_index on contract_workers (department_id); Index created.

Create a view that references the employees and contract_ works tables. The view uses the union collection operation:

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.

Then execute the query against the view:

Select last_name from all_employees_vw where department_id = 50

Because the view is an union collection operation query, the optimizer cannot merge the view's query into the main query block. The optimizer can transform the query by pushing predicates. The where clause condition department_id=50 will be pushed to the view's union collection operation query. The transformed equivalent query is as follows:

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)

The converted query can now consider using an index or a full table scan for each query block, and the execution plan of the query view statement is as follows:

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 Murray- -| Id | Operation | | Name | Starts | E-Rows | E-Bytes | E-Temp | Cost (% CPU) | E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |-- | -| 0 | SELECT STATEMENT | | 1 | 1018 (100) | | 100K | 00lange 01.37 | 955 | 942 | 1 | VIEW | ALL_EMPLOYEES_VW | 1 | 100K | 2637K | | 1018 (1) | 00:00:01 | 100K | 00lange 01.37 | 955 | | 942 | | 2 | SORT UNIQUE | | 1 | 100K | 2540K | 3936K | 1018 (1) | 00:00:01 | 100K | 00lange 01.18 | 955 | 942 | 8416K | 1135K | 7480K (0) | 3 | UNION-ALL | | 1 | | | | 100k | 00VERV 00.76 | 955 | 942 | | * 4 | TABLE ACCESS FULL | EMPLOYEES | 1 | 100K | 2540K | | 273 (1) | 00:00:01 | 100K | 00VERV 00.41 | 948 | 942 | * 5 | TABLE ACCESS FULL | CONTRACT_WORKERS | 1 | | | 1 | 60 | | 2 (0) | 00:00:01 | 1 | 00handlu 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)

You can see from the Predicate Information section of the execution plan that the 4focus 5 operation uses department_id=50 to filter the tables employees and contract_workers, respectively, and proves that predicates can be pushed to the query block in the view.

Thank you for reading this article carefully. I hope the article "how to use predicate push in Oracle 12CR2" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.