17 | 10 | 2017
Latest Articles
Popular Articles

SQL Tuning

Query Transformations : Simple Filter Pushdown

User Rating:  / 0
PoorBest 

Query Transformations : Simple Filter Pushdown

 

Filter pushdown 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 / were bugs related to cost based query transformation causing huge performance degradation )

In the below example I try to show what filter pushdown is and what the impact at explain plan level is.

 

Assume we setup the sample schema

 

SQL> explain plan for select * from VALL_FPD where ts_pk=100;

Explained.

 

SQL> select * from table(dbms_xplan.display);

 

Plan hash value: 976035461

----------------------------------------------------------------------------------

| Id | Operation                  |  Name | Rows | Bytes | Cost (%CPU)|     Time |

----------------------------------------------------------------------------------

|  0 | SELECT STATEMENT           |       |      |       |   172 (100)|          |

|  1 | NESTED LOOPS               |       |  525 | 44625 |     172 (1)| 00:00:01 |

|  2 | TABLE ACCESS BY INDEX ROWID|    TS |    1 |    24 |       1 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN          | TS_PK |    1 |       |       0 (0)|          |

|  4 | VIEW                       |       |  525 | 32025 |     171 (1)| 00:00:01 |

|  5 | UNION-ALL                  |       |      |       |            |          |

|  6 | WINDOW BUFFER              |       |   52 |  1560 |     103 (1)| 00:00:01 |

|* 7 | TABLE ACCESS FULL          |    TC |   52 |  1560 |     103 (1)| 00:00:01 |

|  8 | WINDOW BUFFER              |       |  473 | 10879 |      69 (2)| 00:00:01 |

|* 9 | TABLE ACCESS FULL          |    TI |  473 | 10879 |      69 (2)| 00:00:01 |

----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("TS_PK"=100)                          Filtering Predicate
7 - filter(("TC_DATE3" IS NULL AND "TS_FK"=100)) Filter pushed down into inner view
9 - filter("TS_FK"=100)                          Filter pushed down into inner view

28 rows selected.

 

 

The CBO trace file show us that this is what we call Simple Filter pushdown

 

SQL > alter session set events='10053 trace name context forever, level 1'

SQL > select * from VALL_FPD where ts_pk=100;

SQL > alter session set events='10053 trace name context off';


The trace file in the diagnostic destination show us


####################################################################################

... 

*****************************************************
COST-BASED QUERY TRANSFORMATIONS
******************************************************
FPD: Considering simple filter push (pre rewrite) in query block SEL$F5BB74E1 (#0)
FPD: Current where clause predicates "TS"."TS_PK"=100 AND "TS"."TS_PK"="V"."TS_FK"

 

try to generate transitive predicate from check constraints for query block SEL$F5BB74E1 (#0)
finally: "TS"."TS_PK"=100 AND "V"."TS_FK"=100

 

FPD: Following are pushed to where clause of query block SEL$4 (#0)
"TI"."TS_FK"=100
FPD: Considering simple filter push (pre rewrite) in query block SEL$4 (#0)
FPD: Current where clause predicates "TI"."TS_FK"=100

 

FPD: Following are pushed to where clause of query block SEL$3 (#0)
"TC"."TS_FK"=100
FPD: Considering simple filter push (pre rewrite) in query block SEL$3 (#0)
FPD: Current where clause predicates "TC"."TC_DATE3" IS NULL AND "TC"."TS_FK"=100

...

################################################################################################


FPD could happen here since we declared a foreign key primary key relationship in the data dictionary.

The primary key is referenced in the outer query and thus if there is an added value the filter is pushed down into the inner view. Since this filter decreases the cardinality of the resultset it can be very interesting to achieve this  optimization. Although this example do not show very clearly the added value in more complex queries the added value can be very welcome.

What happens if we do not reference the primary key in the outer query : no filter pushdown happens and cardinality of the inner view do not decrease. 

 

SQL> explain plan for select * from VALL_FPD where ts_name='AASLBFYRHFXMYFRVIGCW';

Explained.

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1792090960

--------------------------------------------------------------------------------------

| Id | Operation         | Name |  Rows | Bytes |TempSpc| Cost (%CPU)|     Time |

--------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT  |      |   500 | 42500 |       |     488 (2)| 00:00:01 |

|* 1 | HASH JOIN         |      |   500 | 42500 |       |     488 (2)| 00:00:01 |

|* 2 | TABLE ACCESS FULL |   TS |     1 |    24 |       |       3 (0)| 00:00:01 |

|  3 | VIEW              |      | 50000 |  2978K|       |     484 (2)| 00:00:01 |

|  4 | UNION-ALL         |      |       |       |       |            |          |

|  5 | WINDOW SORT       |      |  5000 |   146K|       |     104 (2)| 00:00:01 |

|* 6 | TABLE ACCESS FULL |   TC |  5000 |   146K|       |     103 (1)| 00:00:01 |

|  7 | WINDOW SORT       |      | 45000 |  1010K|  1608K|     380 (1)| 00:00:01 |

|  8 | TABLE ACCESS FULL |   TI | 45000 |  1010K|       |      69 (2)| 00:00:01 |

--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("TS_PK"="V"."TS_FK")
2 - filter("TS_NAME"='AASLBFYRHFXMYFRVIGCW') : Filter NOT pushed down
6 - filter("TC_DATE3" IS NULL)

22 rows selected.

 

Only in recent releases , if I am not wrong since 11.2.0.3 filter pushdown can be controlled with "_optimizer_filter_pushdown", It defaults to true so FPD will happen whenever the optimizer think it is appropriate.