Pushing Predicates

Some time ago I wrote a note (on my website) about the push_pred() and no_push_pred() hints. I’ve recently discovered a bug in the 9.2 optimizer that means you may find that Oracle will not use “join predicate pushdown (JPPD)” when it is obviously a good idea.

This note discusses a sequence of execution plans, taken from a system running 9.2.0.8, to demonstrate the point.

We start with a query where the optimizer pushes a predicate through an outer join into a non-mergeable view – this happens because the t2_n1 index is a very effective index and very cheap to use. [Note: the view is non-mergeable for a combination of reasons: it includes a join of its own, and it's the target of an outer join.]

We can see the non-mergeable view appearing in the VIEW operation at line three, but we can also see that the line tells us that predicate pushing took place; moreover the access predicate in line seven tells use that the t3.n1 value is the thing that started life “outside” the view, and has been pushed “inside” the view:

-------------------------------------------------------------------------------
| Id  | Operation                       |  Name       | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |     1 |    46 |     6 |
|   1 |  NESTED LOOPS OUTER             |             |     1 |    46 |     6 |
|*  2 |   TABLE ACCESS FULL             | T3          |     1 |    19 |     4 |
|   3 |   VIEW PUSHED PREDICATE         |             |     1 |    27 |     2 |
|   4 |    TABLE ACCESS BY INDEX ROWID  | T1          |     1 |    16 |     2 |
|   5 |     NESTED LOOPS                |             |     1 |    37 |     4 |
|   6 |      TABLE ACCESS BY INDEX ROWID| T2          |     1 |    21 |     2 |
|*  7 |       INDEX RANGE SCAN          | T2_N1       |     1 |       |     1 |
|*  8 |      INDEX RANGE SCAN           | T1_I1       |     1 |       |     1 |
-------------------------------------------------------------------------------    

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T3"."ID"=1)
   7 - access("T2"."N1"="T3"."N1")
   8 - access("T1"."ID"="T2"."ID")
       filter("T1"."ID">=NVL((),()) AND "T1"."ID"<=())    

The second plan shows the optimizer declining to use predicate pushing for the same data set when I made the t2_n1 index slightly more expensive to use (which I did by using the packaged procedure dbms_stats.set_index_stats() to increase its blevel).

------------------------------------------------------------------------------
| Id  | Operation                      |  Name       | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |     1 |    46 |   284 |
|*  1 |  HASH JOIN OUTER               |             |     1 |    46 |   284 |
|*  2 |   TABLE ACCESS FULL            | T3          |     1 |    19 |     4 |
|   3 |   VIEW                         |             |   250 |  6750 |   279 |
|*  4 |    HASH JOIN                   |             |   250 |  9250 |   279 |
|   5 |     TABLE ACCESS BY INDEX ROWID| T1          |   250 |  4000 |    10 |
|*  6 |      INDEX RANGE SCAN          | T1_I1       |   450 |       |     2 |
|   7 |     TABLE ACCESS FULL          | T2          |   100K|  2050K|   268 |
------------------------------------------------------------------------------    

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("V1"."N1"(+)="T3"."N1")
   2 - filter("T3"."ID"=1)
   4 - access("T1"."ID"="T2"."ID")
   6 - access("T1"."ID">=NVL((),()) AND "T1"."ID"<=())    

Finally, again with the same query and the same dataset and leaving the increased blevel in place for the critical index, I make the t2 table scan that appeared in the second plan more expensive by decreasing the db_file_multiblock_read_count (I don’t have CPU costing enabled in this example, so the optimizer uses the parameter to calculate the tablescan cost, rather than the MBRC value from sys.aux_stats$ – the system statistics).

-------------------------------------------------------------------------------
| Id  | Operation                       |  Name       | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |     1 |    46 |    15 |
|   1 |  NESTED LOOPS OUTER             |             |     1 |    46 |    15 |
|*  2 |   TABLE ACCESS FULL             | T3          |     1 |    19 |    12 |
|   3 |   VIEW PUSHED PREDICATE         |             |     1 |    27 |     3 |
|   4 |    TABLE ACCESS BY INDEX ROWID  | T1          |     1 |    16 |     2 |
|   5 |     NESTED LOOPS                |             |     1 |    37 |     6 |
|   6 |      TABLE ACCESS BY INDEX ROWID| T2          |     1 |    21 |     4 |
|*  7 |       INDEX RANGE SCAN          | T2_N1       |     1 |       |     3 |
|*  8 |      INDEX RANGE SCAN           | T1_I1       |     1 |       |     1 |
-------------------------------------------------------------------------------    

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T3"."ID"=1)
   7 - access("T2"."N1"="T3"."N1")
   8 - access("T1"."ID"="T2"."ID")
       filter("T1"."ID">=NVL((),()) AND "T1"."ID"<=())

This third plan (which is the same as the original plan but with different costs) is the one the optimizer should have used in the second case instead of the “unpushed” plan with the tablescan on t2.  Notice that in the second case the optimizer chose a plan costing 284 over a plan costing 15 – and that shouldn’t be possible!! (In fact, with the original value for the db_file_multiblock_read_count, the correct plan for the second case would have cost only 7, because the table scan of t3 originally cost 4, not 12)

Analysis of the 10053 trace file suggests fairly conclusively that there is a bug in 9i (fixed by 10.2.0.3) in the code that deals with “join predicate pushing”.

The optimizer makes a very silly little mistake. When calculating the total cost of the plan that does not push the join predicate, it has to work out the cost of creating and using the result set from view v1 (which, internally, it labels as from$_subquery$_002).

The cost of creating the result using a hash join between t1 and t2 turns out to be 279. But the size of the result set is rather small – it is the equivalent of a table with a tablescan cost of just 2.

Having worked out the cost of generating the result set, the optimizer then has to work out the cost of joining table t3 with “table” from$_subquery_002$. At this point, I believe that for the purposes of costing the hash join the optimizer uses only the cost of scanning the result set and “forgets” the previously calculated cost of generating the result set.

Unfortunately, to generate the maximum possible confusion, the optimizer still reports the cost of generating the result set in the 10053 trace – with the effect that the trace file seems to show that the cost of the pushed nested loop join is 7, but the cost of the hash join (at 284) is repoted as being less than 7!

I can’t be 100% certain that my hypothesis is correct, and there may be other requirements that have to be met before the anomaly appears. But a few experimental adjustments of the base statistics suggest that it really is just the cost of using the from$_subquery_002 data that optimizer uses to makes its choice – and the cost of generating the result is simply forgotten.

Footnote: This code also seems to demonstrate a second bug. When I added the hint /*+ push_pred(v1) */ to the query, the second plan did not change; the optimizer ignored the hint!

I believe the push_pred hint really is supposed to mean “you will push join predicates unconditionally”, so the hint should have forced the better plan for the second case above. Just to check that I was (probably) using the hint correctly, I also ran the test adding the hint /*+ no_push_pred(v1) */ to the SQL, and the optimizer behaved as I expected – predicate pushing stopped happening in cases one and three as well.