Old-Style (non-cost-based) Join Predicate Push-Down Transformation Part 2 (Recursive WITH)

In a previous blog post I described a bug causing wrong cost calculation when the join predicate push-down transformation (JPPD) is considered in a query containing a hierarchical, i.e. CONNECT BY subquery. In summary, the cost of a parent operation can be lower than its children’s cost. In other words, a child cost doesn’t propagate to its parent. This might give rise to a massively underestimated cost. As a consequence, the optimizer chooses to push a join predicate, even when this is clearly a much worse option. This malfunction seems to be caused by falling back to the old-style (non-cost-based) JPPD (OJPPD).

Luckily, this bug has been resolved in 19.3 – thanks to Timur Akhmeedev for providing this information.

However, if you’re unable to upgrade quickly, you might consider disabling pushing predicates for the affected queries:

opt_param('_push_join_predicate', 'false')

This is, though, something that should be thoroughly tested, as the parameter prevents all predicates from being pushed down – including the good ones.

After publishing the blog post I discovered that CONNECT BY query isn’t the only case affected by this bug. Another is recursive WITH – an ANSI compliant way to query hierarchical data. Generally, CONNECT BY queries can be rewritten as recursive WITH queries. By the way, Laurent Schneider shows us how to do that:

Here’s an example of the execution plan with a recursive WITH subquery and pushed predicates:

with v2(n1,n2) as (
  select n1, n2 from t3 where n1 = 1  
  union all
  select n1, n2 from v2 where n2 = n1 
)
select /*+ qb_name(MAIN) */ t1.n1 from 
  t1,
  ( 
    select /*+ qb_name(RECEIVER) */ t2.n1 from t2, t4
      where t4.n1 = t2.n1
  ) v1,
 v2
 where t1.n1 = v1.n1(+) and t1.n1 = v2.n1 
;

----------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |       |     1 |    39 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN                                 |       |     1 |    39 |     6   (0)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER                       |       |     1 |    26 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL                       | T1    |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |    VIEW PUSHED PREDICATE                   |       |     1 |    13 |     0   (0)| 00:00:01 |
|   5 |     NESTED LOOPS                           |       |     1 |    16 | 27146   (1)| 00:00:02 |
|*  6 |      INDEX UNIQUE SCAN                     | PK_T2 |     1 |    13 |     0   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS FULL                     | T4    |     1 |     3 | 27146   (1)| 00:00:02 |
|   8 |   VIEW                                     |       |     2 |    26 |     4   (0)| 00:00:01 |
|   9 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST|       |       |       |            |          |
|* 10 |     TABLE ACCESS FULL                      | T3    |     1 |    26 |     2   (0)| 00:00:01 |
|* 11 |     RECURSIVE WITH PUMP                    |       |       |       |            |          |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."N1"="V2"."N1")
   6 - access("T2"."N1"="T1"."N1")
   7 - filter("T4"."N1"="T2"."N1" AND "T4"."N1"="T1"."N1")
  10 - filter("N1"=1)
  11 - filter("N2"="N1")

As you can see, the manifestation is the same as with CONNECT BY subquery – the step 4 cost is 0 even though the cost of its child is 27146.

Also, the optimizer trace provides the evidence of the OJPPD fallback.

JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
OJPPD: Promoting index PK_T2
OJPPD: Performing join predicate push-down  to query block RECEIVER (#0)
OJPPD: Pushing predicate "T1"."N1"="V1"."N1"(+)
OJPPD: Used promoted index: PK_T2
OJPPD: Performing join predicate push-down  to query block RECEIVER (#0)
OJPPD: Pushing predicate "T1"."N1"="V1"."N1"(+)
OJPPD: Used promoted index: PK_T2

Unsurprisingly, recursive WITH is also fixed in 19c.

Last but not least, I shared a query for reporting cost propagation errors in execution plans. This query shows, among others, to what extent a database is affected by this problem.