Introduction
The join predicate pushdown (JPPD) transformation allows a view to be joined with index-based nested-loop join method, which may provide a more optimal alternative. In the join predicate pushdown transformation, the view remains a separate query block, but it contains the join predicate, which is pushed down from its containing query block into the view. The view thus becomes correlated and must be evaluated for each row of the outer query block. These pushed-down join predicates, once inside the view, open up new index access paths on the base tables inside the view; this allows the view to be joined with index-based nested-loop join method, thereby enabling the optimizer to select an efficient execution plan.
The join predicate pushdown transformation is not always optimal. The join predicate pushed-down view becomes correlated and it must be evaluated for each outer row; if there is a large number of outer rows, the cost of evaluating the view multiple times may make the nested-loop join suboptimal, and therefore joining the view with hash or sort-merge join method may be more efficient.
The decision whether to push down join predicates into a view is determined by evaluating the costs of the outer query with and without the join predicate pushdown transformation under Oracle's cost-based query transformation framework. The join predicate pushdown transformation applies to both non-mergeable views and mergeable views and to pre-defined and inline views as well as to views generated internally by the optimizer during various transformations.
The following shows the types of views on which join predicate pushdown is currently supported.
- UNION ALL/UNION view
- Outer-joined view
- Anti-joined view
- Semi-joined view
- DISTINCT view
- GROUP-BY view
Prerequisite
Predicate pushing did not happen by default in Oracle 8i. In 8i the hidden parameter "_push_join_predicate" defaults to false whereas it defaults to true in 9i, 10G and 11G.
Predicate pushing is part from query transformations. Query transformation was based on heuristic rules in 9i. From 10G R1 however by default query transformations are cost based since "_optimizer_cost_based_transformation" defaults to linear. ( there are bugs related to cost based query transformation causing huge performance degradation )
In the below example I try to show what predicate pushing is and what the impact at explain plan level is.
Assume we setup the sample schema
Sample
If we run the below SQL we notice that -- at least in our testcase --- Predicate pushing did happens automatically, however we can hint it with the PUSH_PRED hint
select /*+ GATHER_PLAN_STATISTICS PUSH_PRED(inner_view) */ su_pk,su_name,su_comment,inner_view.maxamount from
t_supplier_su,
( select max(or_totalamount) maxamount,su_fk from t_order_or group by su_fk ) inner_view
where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_name is not null;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID gxxa8g94dzk7n, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS PUSH_PRED(inner_view) */
su_pk,su_name,su_comment,inner_view.maxamount from t_supplier_su, (
select max(or_totalamount) maxamount,su_fk from t_order_or group by
su_fk ) inner_view where t_supplier_su.su_pk = inner_view.su_fk(+) and
t_supplier_su.su_name is not null
Plan hash value: 3140464201
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 690K(100)| |
| 1 | NESTED LOOPS OUTER | | 100 | 21800 | 690K (1)| 02:18:01 |
|* 2 | TABLE ACCESS FULL | T_SUPPLIER_SU | 100 | 20300 | 3 (0)| 00:00:01 |
| 3 | VIEW PUSHED PREDICATE | | 1 | 15 | 6900 (1)| 00:01:23 |
| 4 | SORT GROUP BY | | 1 | 9 | 6900 (1)| 00:01:23 |
|* 5 | TABLE ACCESS FULL | T_ORDER_OR | 10101 | 90909 | 6900 (1)| 00:01:23 |
-------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T_SUPPLIER_SU@SEL$1
3 - SEL$639F1A6F / INNER_VIEW@SEL$1
4 - SEL$639F1A6F
5 - SEL$639F1A6F / T_ORDER_OR@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$639F1A6F")
PUSH_PRED(@"SEL$1" "INNER_VIEW"@"SEL$1" 1)
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1")
NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")
LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" "INNER_VIEW"@"SEL$1")
USE_NL(@"SEL$1" "INNER_VIEW"@"SEL$1")
FULL(@"SEL$639F1A6F" "T_ORDER_OR"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$639F1A6F")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T_SUPPLIER_SU"."SU_NAME" IS NOT NULL)
5 - filter("SU_FK"="T_SUPPLIER_SU"."SU_PK")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22],
...................................................................
71 rows selected.
If we run the below SQL we notice that we can avoid predicate pushing (at any cost) with the NO_PUSH_PRED hint.
select /*+ GATHER_PLAN_STATISTICS NO_PUSH_PRED(inner_view) */ su_pk,su_name,su_comment,inner_view.currency,inner_view.maxamount from
t_supplier_su,
( select max(or_totalamount) maxamount,su_fk,cr_fk currency from t_order_or group by su_fk,cr_fk ) inner_view
where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_name is not null;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0gbc9khzuznzk, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS NO_PUSH_PRED(inner_view) */
su_pk,su_name,su_comment,inner_view.currency,inner_view.maxamount from
t_supplier_su, ( select max(or_totalamount) maxamount,su_fk,cr_fk
currency from t_order_or group by su_fk,cr_fk ) inner_view where
t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_name is
not null
Plan hash value: 83112093
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6954 (100)| |
|* 1 | HASH JOIN RIGHT OUTER| | 281 | 66597 | 6954 (2)| 00:01:24 |
| 2 | VIEW | | 281 | 9554 | 6950 (2)| 00:01:24 |
| 3 | HASH GROUP BY | | 281 | 3653 | 6950 (2)| 00:01:24 |
| 4 | TABLE ACCESS FULL | T_ORDER_OR | 1000K| 12M| 6905 (1)| 00:01:23 |
|* 5 | TABLE ACCESS FULL | T_SUPPLIER_SU | 100 | 20300 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / INNER_VIEW@SEL$1
3 - SEL$2
4 - SEL$2 / T_ORDER_OR@SEL$2
5 - SEL$1 / T_SUPPLIER_SU@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1")
NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")
LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" "INNER_VIEW"@"SEL$1")
USE_HASH(@"SEL$1" "INNER_VIEW"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$1" "INNER_VIEW"@"SEL$1")
FULL(@"SEL$2" "T_ORDER_OR"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T_SUPPLIER_SU"."SU_PK"="INNER_VIEW"."SU_FK")
5 - filter("T_SUPPLIER_SU"."SU_NAME" IS NOT NULL)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "T_SUPPLIER_SU"."SU_PK"[NUMBER,22],
...................................................................
"T_SUPPLIER_SU"."SU_NAME"[VARCHAR2,400], "SU_COMMENT"[VARCHAR2,400]
71 rows selected.
Predicate pushing becomes more interesting when the inner view cannot be merged because there is an outer join. If the filtering predicate is selectif predicate pushing can then "push this filter down" into the inner view.
select /*+ GATHER_PLAN_STATISTICS PUSH_PRED(inner_view) */ su_pk,su_name,su_comment,inner_view.currency,inner_view.maxamount from
t_supplier_su,
( select max(or_totalamount) maxamount,su_fk,cr_fk currency from t_order_or group by su_fk,cr_fk ) inner_view
where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_pk= 1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID bw56zj2jw7vng, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS PUSH_PRED(inner_view) */
su_pk,su_name,su_comment,inner_view.currency,inner_view.maxamount from
t_supplier_su, ( select max(or_totalamount) maxamount,su_fk,cr_fk
currency from t_order_or group by su_fk,cr_fk ) inner_view where
t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_pk= 1
Plan hash value: 484793169
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6902 (100)| |
| 1 | NESTED LOOPS OUTER | | 3 | 678 | 6902 (1)| 00:01:23 |
| 2 | TABLE ACCESS BY INDEX ROWID | T_SUPPLIER_SU | 1 | 203 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SU_PK | 1 | | 0 (0)| |
| 4 | VIEW PUSHED PREDICATE | | 3 | 69 | 6901 (1)| 00:01:23 |
| 5 | SORT GROUP BY | | 3 | 39 | 6901 (1)| 00:01:23 |
|* 6 | FILTER | | | | | |
|* 7 | TABLE ACCESS FULL | T_ORDER_OR | 11636 | 147K| 6900 (1)| 00:01:23 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T_SUPPLIER_SU@SEL$1
3 - SEL$1 / T_SUPPLIER_SU@SEL$1
4 - SEL$639F1A6F / INNER_VIEW@SEL$1
5 - SEL$639F1A6F
7 - SEL$639F1A6F / T_ORDER_OR@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$639F1A6F")
PUSH_PRED(@"SEL$1" "INNER_VIEW"@"SEL$1" 1)
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" ("T_SUPPLIER_SU"."SU_PK"))
NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")
LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" "INNER_VIEW"@"SEL$1")
USE_NL(@"SEL$1" "INNER_VIEW"@"SEL$1")
FULL(@"SEL$639F1A6F" "T_ORDER_OR"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$639F1A6F")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T_SUPPLIER_SU"."SU_PK"=1)
6 - filter(1="T_SUPPLIER_SU"."SU_PK")
7 - filter("SU_FK"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22], "SU_NAME"[VARCHAR2,400],
...................................................................
77 rows selected.
Another example of interesting predicate pushing is the example below. For the supplier 100 there are no orders (read no records in the inner view).
We need however an entry in the result set for supplier 100, thus we need an outer join.
The filtering predicate results in the usage of the index OR_IDX1, we only need (selectif) records for 1 supplier. ( supplier 1 )
select /*+ GATHER_PLAN_STATISTICS NO_MERGE(inner_view) PUSH_PRED(inner_view) */ su_pk,su_name,su_comment,inner_view.currency,inner_view.maxamount from
t_supplier_su,
( select max(or_totalamount) maxamount,su_fk,cr_fk currency from t_order_or group by su_fk,cr_fk ) inner_view
where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_pk in (1,100);
SU_PK
----------
SU_NAME
--------------------------------------------------------------------------------
SU_COMMENT
--------------------------------------------------------------------------------
CURRENCY MAXAMOUNT
------------ ----------
1
ZAQMGJLUHAZPEVKNGPXEWNYVKUPXMDENWSGCWAFRBRJMNABHOLVHESPKVFKTMQIESGRBVPFIHHRKOZCZ
...................................................................
We love SQL tuning
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 997w5gw1rvgdu, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS NO_MERGE(inner_view)
PUSH_PRED(inner_view) */ su_pk,su_name,su_comment,inner_view.currency,in
ner_view.maxamount from t_supplier_su, ( select max(or_totalamount)
maxamount,su_fk,cr_fk currency from t_order_or group by su_fk,cr_fk )
inner_view where t_supplier_su.su_pk = inner_view.su_fk(+) and
t_supplier_su.su_pk in (1,100)
Plan hash value: 331405424
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 299(100)| |
| 1 | NESTED LOOPS OUTER | | 2 | 452 | 299 (1)| 00:00:04 |
| 2 | INLIST ITERATOR | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | T_SUPPLIER_SU | 2 | 406 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SU_PK | 2 | | 1 (0)| 00:00:01 |
| 5 | VIEW PUSHED PREDICATE | | 1 | 23 | 148 (1)| 00:00:02 |
| 6 | SORT GROUP BY | | 6 | 78 | 148 (1)| 00:00:02 |
|* 7 | FILTER | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | T_ORDER_OR | 151 | 1963 | 147 (0)| 00:00:02 |
|* 9 | INDEX RANGE SCAN | OR_IDX1 | 151 | | 22 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T_SUPPLIER_SU@SEL$1
4 - SEL$1 / T_SUPPLIER_SU@SEL$1
5 - SEL$639F1A6F / INNER_VIEW@SEL$1
6 - SEL$639F1A6F
8 - SEL$639F1A6F / T_ORDER_OR@SEL$2
9 - SEL$639F1A6F / T_ORDER_OR@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$639F1A6F")
PUSH_PRED(@"SEL$1" "INNER_VIEW"@"SEL$1" 1)
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" ("T_SUPPLIER_SU"."SU_PK"))
NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")
LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" "INNER_VIEW"@"SEL$1")
USE_NL(@"SEL$1" "INNER_VIEW"@"SEL$1")
INDEX_RS_ASC(@"SEL$639F1A6F" "T_ORDER_OR"@"SEL$2" ("T_ORDER_OR"."SU_FK"))
USE_HASH_AGGREGATION(@"SEL$639F1A6F")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(("T_SUPPLIER_SU"."SU_PK"=1 OR "T_SUPPLIER_SU"."SU_PK"=100))
7 - filter((1="T_SUPPLIER_SU"."SU_PK" OR 100="T_SUPPLIER_SU"."SU_PK"))
9 - access("SU_FK"="T_SUPPLIER_SU"."SU_PK")
filter(("SU_FK"=1 OR "SU_FK"=100))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22], "SU_NAME"[VARCHAR2,400],
...................................................................
9 - "T_ORDER_OR".ROWID[ROWID,10], "SU_FK"[NUMBER,22]
85 rows selected.Consider another example of a query , which contains a view with the UNION ALL set operator.
select /*+ push_pred(v) */ prod.* from PROMOTIONS prod, (
select p.PROD_ID,AMOUNT_SOLD,s.PROMO_ID from sales s, PRODUCTS p
where s.prod_id = p.prod_id
union all
select p.PROD_ID,UNIT_PRICE,c.PROMO_ID from COSTS c, PRODUCTS p
where c.prod_id = p.prod_id) V
where prod.PROMO_ID=v.PROMO_ID;
select * from TABLE(dbms_xplan.display_cursor(NULL, NULL, 'ADVANCED'));
Plan hash value: 1442795797
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 97M| 284K (2)| 00:56:58 | | |
| 1 | NESTED LOOPS | | 1000K| 97M| 284K (2)| 00:56:58 | | |
| 2 | TABLE ACCESS FULL | PROMOTIONS | 503 | 49294 | 17 (0)| 00:00:01 | | |
| 3 | VIEW | | 511 | 2044 | 566 (2)| 00:00:07 | | |
| 4 | UNION ALL PUSHED PREDICATE | | | | | | | |
|* 5 | HASH JOIN | | 229K| 2691K| 492 (3)| 00:00:06 | | |
| 6 | INDEX FULL SCAN | PRODUCTS_PK | 72 | 288 | 1 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE ALL | | 229K| 1794K| 489 (2)| 00:00:06 | 1 | 28 |
|* 8 | TABLE ACCESS FULL | SALES | 229K| 1794K| 489 (2)| 00:00:06 | 1 | 28 |
| 9 | NESTED LOOPS | | 27371 | 320K| 75 (3)| 00:00:01 | | |
| 10 | PARTITION RANGE ALL | | 27371 | 213K| 74 (2)| 00:00:01 | 1 | 28 |
|* 11 | TABLE ACCESS FULL | COSTS | 27371 | 213K| 74 (2)| 00:00:01 | 1 | 28 |
|* 12 | INDEX UNIQUE SCAN | PRODUCTS_PK | 1 | 4 | 0 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / PROD@SEL$1
3 - SET$5715CE2E / V@SEL$1
4 - SET$5715CE2E
5 - SEL$639F1A6F
6 - SEL$639F1A6F / P@SEL$2
8 - SEL$639F1A6F / S@SEL$2
9 - SEL$B01C6807
11 - SEL$B01C6807 / C@SEL$3
12 - SEL$B01C6807 / P@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PX_JOIN_FILTER(@"SEL$639F1A6F" "S"@"SEL$2")
USE_HASH(@"SEL$639F1A6F" "S"@"SEL$2")
LEADING(@"SEL$639F1A6F" "P"@"SEL$2" "S"@"SEL$2")
FULL(@"SEL$639F1A6F" "S"@"SEL$2")
INDEX(@"SEL$639F1A6F" "P"@"SEL$2" ("PRODUCTS"."PROD_ID"))
USE_NL(@"SEL$B01C6807" "P"@"SEL$3")
LEADING(@"SEL$B01C6807" "C"@"SEL$3" "P"@"SEL$3")
INDEX(@"SEL$B01C6807" "P"@"SEL$3" ("PRODUCTS"."PROD_ID"))
FULL(@"SEL$B01C6807" "C"@"SEL$3")
USE_NL(@"SEL$1" "V"@"SEL$1")
LEADING(@"SEL$1" "PROD"@"SEL$1" "V"@"SEL$1")
NO_ACCESS(@"SEL$1" "V"@"SEL$1")
FULL(@"SEL$1" "PROD"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SET$1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
PUSH_PRED(@"SEL$1" "V"@"SEL$1" 1)
OUTLINE_LEAF(@"SET$5715CE2E")
OUTLINE_LEAF(@"SEL$B01C6807")
OUTLINE_LEAF(@"SEL$639F1A6F")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("S"."PROD_ID"="P"."PROD_ID")
8 - filter("S"."PROMO_ID"="PROD"."PROMO_ID")
11 - filter("C"."PROMO_ID"="PROD"."PROMO_ID")
12 - access("C"."PROD_ID"="P"."PROD_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "PROD"."PROMO_ID"[NUMBER,22], "PROD"."PROMO_NAME"[VARCHAR2,30],
...................................................................
11 - "C"."PROD_ID"[NUMBER,22], "C"."PROMO_ID"[NUMBER,22]
the above, 'VIEW UNION ALL PUSHED PREDICATE' indicates that the UNION ALL view has undergone the join predicate pushdown transformation. As can be seen, here the join predicate has been replicated and pushed inside every branch of the UNION ALL view. The join predicates (shown here in red) open up index access paths thereby enabling index-based nested loop join of the view.
Consider following query as an example of join predicate pushdown into a distinct view. We have the following cardinalities of the tables involved in this query : Sales (1,016,271), Customers (50,000), and Costs (787,766).
SELECT C.cust_last_name, C.cust_city
FROM customers C,
(SELECT DISTINCT S.cust_id
FROM sales S, costs CT
WHERE S.prod_id = CT.prod_id and CT.unit_price > 70) V
WHERE C.cust_state_province = 'CA' and C.cust_id = V.cust_id;
Plan hash value: 3834618923
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 1764 | 49900 (2)| 00:09:59 | | |
| 1 | NESTED LOOPS SEMI | | 49 | 1764 | 49900 (2)| 00:09:59 | | |
|* 2 | TABLE ACCESS FULL | CUSTOMERS | 383 | 13022 | 405 (1)| 00:00:05 | | |
| 3 | VIEW PUSHED PREDICATE | | 18205 | 36410 | 129 (2)| 00:00:02 | | |
|* 4 | HASH JOIN | | 143K| 2515K| 129 (2)| 00:00:02 | | |
| 5 | PARTITION RANGE ALL | | 130 | 1170 | 54 (0)| 00:00:01 | 1 | 28 |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 130 | 1170 | 54 (0)| 00:00:01 | 1 | 28 |
| 7 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 8 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | | | | 1 | 28 |
| 9 | PARTITION RANGE ALL | | 79172 | 695K| 74 (2)| 00:00:01 | 1 | 28 |
|* 10 | TABLE ACCESS FULL | COSTS | 79172 | 695K| 74 (2)| 00:00:01 | 1 | 28 |
------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / C@SEL$1
3 - SEL$639F1A6F / V@SEL$1
4 - SEL$639F1A6F
6 - SEL$639F1A6F / S@SEL$2
10 - SEL$639F1A6F / CT@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PX_JOIN_FILTER(@"SEL$639F1A6F" "CT"@"SEL$2")
USE_HASH(@"SEL$639F1A6F" "CT"@"SEL$2")
LEADING(@"SEL$639F1A6F" "S"@"SEL$2" "CT"@"SEL$2")
FULL(@"SEL$639F1A6F" "CT"@"SEL$2")
BITMAP_TREE(@"SEL$639F1A6F" "S"@"SEL$2" AND(("SALES"."CUST_ID")))
USE_NL(@"SEL$1" "V"@"SEL$1")
LEADING(@"SEL$1" "C"@"SEL$1" "V"@"SEL$1")
NO_ACCESS(@"SEL$1" "V"@"SEL$1")
FULL(@"SEL$1" "C"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
PUSH_PRED(@"SEL$1" "V"@"SEL$1" 3)
OUTLINE_LEAF(@"SEL$639F1A6F")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C"."CUST_STATE_PROVINCE"='CA')
4 - access("S"."PROD_ID"="CT"."PROD_ID")
8 - access("S"."CUST_ID"="C"."CUST_ID")
10 - filter("CT"."UNIT_PRICE">70)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "C"."CUST_LAST_NAME"[VARCHAR2,40], "C"."CUST_CITY"[VARCHAR2,30]
...................................................................
10 - "CT"."PROD_ID"[NUMBER,22]
As shown above, when query D undergoes join predicate pushdown transformation, the expensive DISTINCT operator is removed and the join is converted into a semi-join; this is possible, since all the SELECT list items of the view participate in an equi-join with the outer tables. Under similar conditions, when a group-by view undergoes join predicate pushdown transformation, the expensive group-by operator can also be removed.
With the join predicate pushdown transformation, the elapsed time of query D came down from 63 seconds to 5 seconds.
Since distinct and group-by views are mergeable views, the cost-based transformation framework also compares the cost of merging the view with that of join predicate pushdown in selecting the most optimal execution plan.
Remember we can avoid that any query transformation ahppens with the NO_QUERY_TRANSFORMATION hint.
SQL> select /*+ GATHER_PLAN_STATISTICS NO_QUERY_TRANSFORMATION */ su_pk,su_name,su_comment,inner_view.currency,
2 inner_view.maxamount from t_supplier_su, ( select max(or_totalamount)
maxamount,su_fk,cr_fk currency from t_order_or group by su_fk,cr_fk )
inner_view where t_supplier_su.su_pk = inner_view.su_fk(+) and
t_supplier_su.su_pk in (1,100) ;
Tuning case
早 上某数据库服务器CPU不断报警，应用系统管理员同时反馈应用响应明显变慢。登陆数据库主机查看，应用连接上来的几个进程占用了大量的CPU资源，造成 CPU空闲率很低。登陆数据库查询，发现有不少buffer cache chains的等待，初步判断是应用上出现了某些性能糟糕的SQL语句。通过进程捕获了几条耗资源的SQL语句，发现大部分都是类似同一条语句造成的。手 工执行一下，需要2分多钟才能出结果。捕获到的SQL语句如下：
SELECT *
FROM (SELECT DISTINCT e.n_event_id,
e.n_parent_id, e.v_event_num, em.n_req_type_1, em.n_req_type_2, em.v_title, em.v_description, e.n_priority, cb.n_time_limit, cb.n_status, e.n_process_way, e.n_maintaince_event_id, e.v_maintaince_flag, e.v_replacedevice_flag, et.d_acbept_date, et.d_finish_date, et.v_exempt_status, et.n_callback_status, et.n_delay_time, erpl.n_creator, erpl.d_creation_date, e.n_req_id, el.v_res_notice_msg, el.v_res_notice_email, el.v_res_notice_im, vd.v_valid_status, vd.v_related_org_id, e.n_dev_process_way, e.v_over_time_reason, e.v_confirm_person, e.v_new_device_num
FROM tb_event e, tb_event_related_person erpl, vorg_department vd, tb_callback cb, tb_event_log el, tb_event_marker em, tb_event_track et
WHERE e.n_event_id = et.n_event_id(+) AND e.n_event_id = em.n_event_id(+) AND e.n_event_id = el.n_event_id(+) AND e.n_event_id = cb.n_event_id(+) AND erpl.n_dept_id = vd.recid(+) AND e.n_event_id = erpl.n_event_id(+)
ORDER BY e.d_creation_date DESC)
WHERE rownum <= 40;
vorg_department为一个view，具体定义如下：
create or replace view vorg_department as
select d.recid, r.v_valid_status, d.v_related_org_id, r.org_parent, r.tree_no, d.dept_kind, d.dept_type, d.dept_name, d.status, d.dept_code, d.area_code, d.dept_prof, d.sort_num, d.link_addr, r.layer
from ORG_DEPARTMENT d, ORG_DEPRELATION r
where d.recid = r.org_child
and r.relation_type = 1
该语句的执行计划如下：
PLAN_TABLE_OUTPUT
———————————————————————————————————–
| Id | Operation | Name | Rows | Bytes|TempSpc|Cost|
———————————————————————————————————–
| 0 | SELECT STATEMENT | | 40| 15404| | 23M|
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 110M| 395G| | 23M|
|* 3 | SORT UNIQUE STOPKEY | | 110M| 27G| 58G| 11M|
| 4 | NESTED LOOPS OUTER | | 110M| 27G| | 2797|
|* 5 | HASH JOIN OUTER | | 1255K| 308M| 39M| 2797|
|* 6 | HASH JOIN OUTER | | 153K| 37M| 28M| 1873|
|* 7 | HASH JOIN OUTER | | 115K| 27M| 24M| 1384|
|* 8 | HASH JOIN OUTER | | 101K| 23M| 23M| 990|
|* 9 | HASH JOIN OUTER | | 100K| 22M| 17M| 571|
| 10 | TABLE ACCESS FULL | TB_EVENT | 77044| 16M| | 256|
| 11 | INDEX FAST FULL SCAN| IDX_EVENT_TRACK_N_E_ID| 100K| 491K| | 4|
| 12 | INDEX FAST FULL SCAN |IDX_TB_CALLBAK_E_ID | 75959| 296K| | 21|
| 13 | TABLE ACCESS FULL | TB_EVENT_MARKER | 3686| 18430| | 3|
|* 14 | INDEX FAST FULL SCAN | IDX_TB_EVENT_RP_DUP1 | 101K| 895K| | 4|
| 15 | INDEX FAST FULL SCAN |IDX_TB_EVENT_LOG_N_E_ID| 628K| 2455K| | 149|
| 16 | VIEW PUSHED PREDICATE | VORG_DEPARTMENT | 88| 1144 | | |
| 17 | NESTED LOOPS | | 1 | 15 | | 2|
|* 18 | INDEX UNIQUE SCAN | PK_DEPARTMENT | 1 | 6| | 1|
|* 19 | INDEX RANGE SCAN | ASSOCIATION8_FK | 1 | 9| | 1|
———————————————————————————————————–
首 先了解了一下各表的统计信息情况和表的数据情况，统计信息都是最新的，除了tb_event_log表略微大点，有60多万数据外，其余表均10万左右或 更小，执行计划里面的小表table full sacn和一些索引的access情况也并无严重问题。接下来关注表与表之间的关联顺序和方式。继续分析这个执行计划，第四行评估出了一个超级大的结果 集，找到这个结果集的产生方式，第16行引起了我的注意。VIEW PUSHED PREDICATE,在存在out join和view的情况下，CBO自动选择了谓词推进到了视图VORG_DEPARTMENT中以过滤更多的数据，也算无可厚非。 但继续往上找，找到这个谓词的基数，第5行，发现评估出来的基数竟然有100多万行之多，而视图本身的结果集，只有不到3000行！看来这是一个“愚蠢 的”执行计划，CBO先选择视图之外的表做关联,tb_event虽然本身很小，但由于与其他表做多次外连接， 因此最终得到一个庞大的基数，而拿着这个结果集推进到视图中，想想是多么恐怖的事情!一般来说，大集合的结果集合并不适合进行PUSHED PREDICATE。如果只是少量，会起到比较好的效果。
既然知道了原因，那就尝试着去改变这个执行计划。Oracle提供了no_push_pred和push_pred来改变pushed predication行为，在上述语句中添加no_push_pred提示：
SELECT *
FROM (SELECT /*+ no_push_pred(vd) */ DISTINCT e.n_event_id,
e.n_parent_id,
e.v_event_num,
….
FROM tb_event e,
tb_event_related_person erpl,
vorg_department vd,
tb_callback cb,
tb_event_log el,
tb_event_marker em,
tb_event_track et
WHERE e.n_event_id = et.n_event_id(+)
AND e.n_event_id = em.n_event_id(+)
AND e.n_event_id = el.n_event_id(+)
AND e.n_event_id = cb.n_event_id(+)
AND erpl.n_dept_id = vd.recid(+)
AND e.n_event_id = erpl.n_event_id(+)
ORDER BY e.d_creation_date DESC)
WHERE rownum <= 40;
修改后的执行计划如下：
———————————————————————————————————–
| Id | Operation | Name | Rows | Bytes|TempSpc|Cost|
———————————————————————————————————–
| 0 | SELECT STATEMENT | | 40 | 11553| | 181K|
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1470K| 5398M| | 181K|
|* 3 | SORT UNIQUE STOPKEY | | 1470K| 370M| 765M| 92546|
|* 4 | HASH JOIN OUTER | | 1470K| 370M| 46M| 3546|
|* 5 | HASH JOIN OUTER | | 180K| 44M| 39M| 2499|
|* 6 | HASH JOIN OUTER | | 153K| 37M| 28M| 1873|
|* 7 | HASH JOIN OUTER | | 115K| 27M| 24M| 1384|
|* 8 | HASH JOIN OUTER | | 101K| 23M| 23M| 990|
|* 9 | HASH JOIN OUTER | | 100K| 22M| 17M| 571|
| 10 | TABLE ACCESS FULL | TB_EVENT | 77044| 16M| | 256|
| 11 | INDEX FAST FULL SCAN| IDX_EVENT_TRACK_N_E_ID | 100K| 491K| | 4|
| 12 | INDEX FAST FULL SCAN | IDX_TB_CALLBAK_E_ID | 75959| 296K| | 21|
| 13 | TABLE ACCESS FULL | TB_EVENT_MARKER | 3686| 18430| | 3|
|* 14 | INDEX FAST FULL SCAN | IDX_TB_EVENT_RP_DUP1 | 101K| 895K| | 4|
| 15 | VIEW | VORG_DEPARTMENT | 3355| 20130| | 3|
| 16 | NESTED LOOPS | | 3355| 50325| | 3|
|* 17 | INDEX FAST FULL SCAN | ASSOCIATION8_FK | 3356| 30204| | 3|
|* 18 | INDEX UNIQUE SCAN | PK_DEPARTMENT | 1| 6| | |
| 19 | INDEX FAST FULL SCAN | IDX_TB_EVENT_LOG_N_E_ID| 628K| 2455K| | 149|
———————————————————————————————————
表tb_event e和tb_event_track et关联 通过e 和 tb_callback cb关联 通过 e和 tb_event_marker em,
调 整后整个执行速度有了明显提升，15秒之内可以返回结果，可以看到view结果集与第六步产生的结果集进行hash join outer，然后得到的结果集合与最大的表tb_event_log再次进行hash join outer。当然这个执行计划可能还有完善的余地，需进一步根据数据情况调整表的关联顺序.
参考至:http://www.online-database.eu/index.php/sql-tuning/180-predicate-pushing
https://blogs.oracle.com/optimizer/entry/basics_of_join_predicate_pushdown_in_oracle
http://blog.csdn.net/zhaoyangjian724/article/details/17121707
本文原创，转载请注明出处、作者
如有错误，欢迎指正
邮箱:czmcj@163.com
相关推荐
Oracle 8i 开始有四种转换技术：视图合并（View Merging）、谓词推进（Predicate Pushing）、非嵌套子查询（Subquery Unnesting）和物化视图的查询重写（Query Rewrite with Materialized Views）。 CBO 优化器可以...
Predicate是编程语言中的一种常见概念，特别是在面向对象的语言如Java、C#等中，它代表一个可以返回布尔值的函数或方法，常用于过滤、查询数据。Predicate的主要用途是作为参数传递给各种高阶函数，比如`filter()`、...
- **Predicate Pushing**：将过滤条件尽可能下推到基表中，减少返回的数据量。 - **Materialized Views**：预先计算并存储复杂查询的结果，提高查询性能。 #### 四、内存管理 Oracle数据库的性能在很大程度上取决...
谓词委托通常与泛型委托`Predicate<T>`一起使用，其中`T`代表委托处理的数据类型。 首先，让我们了解一下谓词。在逻辑学中，谓词是对实体（如对象或值）进行判断的表达式，它返回真或假。在编程中，谓词是一个返回...
3. Predicate（谓词）：一个查询中的 WHERE 限制条件。 4. Driving Table（驱动表）：该表又称为外层表（OUTER TABLE）。这个概念用于嵌套与 HASH 连接中。如果该 Row Source 返回较多的行数据，则对所有的后续操作...
Predicate则是查询中的WHERE子句，用于过滤不符合条件的行。Driving Table和Probed Table是用于描述连接操作的术语，Driving Table（外层表）通常是返回行较少、对查询性能影响较小的表，而Probed Table（内层表）...
ORACLE 执行计划和 SQL 调优 ORACLE 执行计划和 SQL 调优是关系数据库管理系统中非常重要的概念。执行计划是 Oracle 优化器生成的，用于描述如何访问数据库中的数据的计划。execute plan 中包括了访问路径、表扫描...
标题中的"ConsoleApplication2_c#predicate_"表明这是一个关于C#编程的示例应用，其中可能涉及到了Predicate委托的使用。Predicate在C#中是一个代表返回布尔值的委托类型，常用于 LINQ 查询表达式中进行条件判断。...
【Oracle执行计划和SQL调优】是数据库管理中至关重要的环节，主要涉及到如何高效地运行SQL语句，提高数据库性能。下面将详细讲解执行计划的相关概念以及SQL调优的策略。 1. **Rowid的概念**：Rowid是Oracle数据库中...
其中，四个关键的接口——Consumer、Supplier、Predicate和Function，是Lambda表达式的重要组成部分，帮助开发者处理各种操作，尤其是处理集合数据时。下面将详细讲解这四个接口及其在实际开发中的应用。 1. **...
4. **Predicate**： 谓词是SQL查询中的WHERE子句，用来限定返回的数据。它们决定了哪些行符合查询条件。 5. **Driving Table (驱动表)**： 驱动表，也称为外层表，是在查询的连接操作中首先被访问的表。理想的...
Predicate，即谓词，是查询中的WHERE子句，用于指定筛选条件。在执行计划中，谓词的优化直接影响到查询效率，合理的谓词可以帮助减少扫描的数据量。 Driving Table和Probed Table是用于描述连接操作的两个关键概念...
谓词（Predicate）是Java 8引入的一种新特性，它代表了一个接收单个输入参数并返回布尔值的函数。这个概念源自数学，允许我们以一种声明性的方式表达条件。在Java中，`java.util.function.Predicate`接口就是谓词的...
- 函数 `select_Imt` 首先初始化 `rtn_predicate` 为 `'1=1'`，这意味着初始条件始终为真。 - 如果当前用户为 `SCOTT`，那么访问条件变为 `deptno=sys_context('empenv','scott_attr1')`，即只有当部门编号等于 `...