The optimizer sometimes performs an extra calculation when doing a Join Predicate Push Down (JPPD) transformation. I mean the step that is annotated as “CBQT Join Predicate Push Down Additional Phase”. The purpose of this blog post is to show what triggers this phase.
I’ll be using the model consisting of the following four tables:
create table t1
(
n1 number,
n2 number
) ;
insert into t1 select level,level from dual connect by level <= 1 ;
exec dbms_stats.gather_table_stats(null,'t1') ;
create table t2
(
n1 number not null,
n2 number
) ;
insert into t2 select level,level from dual connect by level <= 400000 ;
create index ix_t2_1 on t2 (n2) ;
create unique index pk_t2 on t2 (n1) ;
exec dbms_stats.gather_table_stats(null,'t2', cascade => true ) ;
create table t3
(
n1 number,
n2 number
) ;
insert into t3 select level, level from dual connect by level <= 1100000 ;
create index ix_t3_1 on t3 (n2) ;
exec dbms_stats.gather_table_stats(null,'t3', cascade => true ) ;
create table t4
(
n1 number not null
);
insert into t4 select level from dual connect by level <= 300000 ;
create unique index pk_t4 on t4 (n1) ;
exec dbms_stats.gather_table_stats(null,'t4', cascade => true ) ;Let’s take a closer look at the following query:
select /*+ qb_name(QB_MAIN) */
t1.n2
from t1 ,
( select /*+ qb_name(QB_MIDDLE) */ t2.n2
from
t2 ,
( select /*+ qb_name(QB_INNER ) */ t3.n2
from t3, t4
where t4.n1 = t3.n1
) v2
where v2.n2(+) = t2.n1
) v1
where v1.n2(+) = t1.n1 ;Besides the main query block (QB) QB_MAIN, the query has two nested QBs: QB_MIDDLE and QB_INNER, both of which are the candidates for JPPD.
The optimizer first considered, but rejected the JPPD from QB_MIDDLE to QB_INNER:
2403 JPPD: Not update best state, Cost = 997.835318
2404 JPPD: Will not use JPPD from query block QB_MIDDLE (#2)
Then it considered the JPPD from QB_MAIN to QB_MIDDLE:
2407 JPPD: Checking validity of push-down from query block QB_MAIN (#1) to query block QB_MIDDLE (#2))Finally, it decided to do the JPPD from QB_MAIN to QB_MIDDLE:
3663 JPPD: Will use JPPD from QB_MAIN (#1) to QB_MIDDLE (#2).It’s worth noting that the whole calculation for the JPPD from QB_MAIN to QB_MIDDLE was done for the case when the join predicate was not pushed from QB_MIDDLE to QB_INNER. We can additionally confirm that by looking at the excerpt below:
2406 JPPD: Checking validity of push-down in query block QB_MAIN (#1)
2407 JPPD: Checking validity of push-down from query block QB_MAIN (#1) to query block QB_MIDDLE (#2)
2408 Check Basic Validity for Non-Union View for query block QB_MIDDLE (#2)
2409 JPPD: Passed validity checks
2410 JPPD: JPPD: Pushdown from query block QB_MAIN (#1) passed validity checks.
2411 Join-Predicate push-down on query block QB_MAIN (#1)
2412 JPPD: Using search type: linear
2413 JPPD: Considering join predicate push-down
2414 JPPD: Starting iteration 1, state space = (2) : (0)
2415 JPPD: Performing join predicate push-down (no transformation phase) from query block QB_MAIN (#1) to query block QB_MIDDLE (#2)
...
3022 Final cost for query block QB_MIDDLE (#2) - All Rows Plan:
3023 Best join order: 1
3024 Cost: 997.835318 Degree: 1 Card: 400000.000000 Bytes: 9200000.000000
3025 Resc: 997.835318 Resc_io: 946.000000 Resc_cpu: 2491430510
...
3218 JPPD: Updated best state, Cost = 1002.687203
3219 JPPD: Starting iteration 2, state space = (2) : (1)
3220 JPPD: Performing join predicate push-down (candidate phase) from query block QB_MAIN (#1) to query block QB_MIDDLE (#2)
3221 JPPD: Pushing predicate "V1"."N2"(+)="T1"."N1"
3222 from query block QB_MAIN (#1) to query block QB_MIDDLE (#2)
3223 JPPD: Push dest of pred 0x869e2740 is qb 0x869d6450:query block QB_MIDDLE (#2)When calculating the cost of JPPD from QB_MAIN to QB_MIDDLE, the optimizer used the cost 997.835318 for QB_MIDDLE, which is the cost when JPPD from QB_MIDDLE to QB_INNER isn’t performed.
The total cost of such a transformed query is 362.206376:
3649 Final cost for query block QB_MAIN (#1) - All Rows Plan:
3650 Best join order: 1
3651 Cost: 362.206376 Degree: 1 Card: 1.000000 Bytes: 8.000000In other words, what has been calculated so far is the query cost when the JPPD from QB_MAIN to QB_MIDDLE is done, but from QB_MIDDLE to QB_INNER isn’t. The reason for avoiding the JPPD from QB_MIDDLE to QB_INNER is the cost calculation when optimizing QB_MIDDLE for itself, as indicated on the line 2404.
But what if the JPPD from QB_MIDDLE to QB_INNER would yield a better plan when done in conjunction with the JPPD from QB_MAIN to QB_MIDDLE? This combination hasn’t been considered so far.
This is exactly where the JPPD Additional Phase kicks in. Simply put, it calculates the combination of transformed QBs even when the decision has been previously made not to transform individual QBs:
3665 CBQT Join Predicate Push Down Additional Phase
3666 JPPD: Checking validity of push-down in query block QB_MIDDLE (#2)
3667 JPPD: JPPD: Pushdown from query block QB_MIDDLE (#2) passed validity checks.
3668 JPPD: Performing join predicate push-down (final phase) from query block QB_MIDDLE (#2) to query block QB_INNER (#3)
3669 JPPD: Pushing predicate "V2"."N2"(+)="T2"."N1"
3670 from query block QB_MIDDLE (#2) to query block QB_INNER (#3)
3671 JPPD: Push dest of pred 0xc16b1868 is qb 0x89c84958:query block QB_INNER (#3)
3672
3673
3674 JPPD: Checking validity of push-down in query block QB_MAIN (#1)
3675 JPPD: JPPD: Pushdown from query block QB_MAIN (#1) passed validity checks.
3676 JPPD: Performing join predicate push-down (final phase) from query block QB_MAIN (#1) to query block QB_MIDDLE (#2)
3677 JPPD: Pushing predicate "V1"."N2"(+)="T1"."N1"
3678 from query block QB_MAIN (#1) to query block QB_MIDDLE (#2)
3679 JPPD: Push dest of pred 0xc16b4500 is qb 0x89c84458:query block QB_MIDDLE (#2)As we can see, the optimizer reverted its previous decision not to do the JPPD from QB_MIDDLE to QB_INNER.
It turns out that this combination yields the best execution plan:
5056 Final cost for query block QB_MAIN (#1) - All Rows Plan:
5057 Best join order: 1
5058 Cost: 8.002284 Degree: 1 Card: 1.000000 Bytes: 8.000000
----- Plan Table -----
============
Plan Table
============
----------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 8 | |
| 1 | NESTED LOOPS OUTER | | 1 | 8 | 8 | 00:00:01 |
| 2 | TABLE ACCESS FULL | T1 | 1 | 6 | 4 | 00:00:01 |
| 3 | VIEW PUSHED PREDICATE | | 1 | 2 | 4 | 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 12 | 4 | 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 10 | 2 | 00:00:01 |
| 6 | INDEX RANGE SCAN | IX_T2_1 | 1 | | 1 | 00:00:01 |
| 7 | VIEW PUSHED PREDICATE | | 1 | 2 | 2 | 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 15 | 2 | 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 10 | 2 | 00:00:01 |
| 10 | INDEX RANGE SCAN | IX_T3_1 | 1 | | 1 | 00:00:01 |
| 11 | INDEX UNIQUE SCAN | PK_T4 | 1 | 5 | 0 | |
----------------------------------------------------+-----------------------------------+
Query Block Name / Object Alias(identified by operation id):
------------------------------------------------------------
1 - QB_MAIN
2 - QB_MAIN / T1@QB_MAIN
3 - SEL$5481112B / V1@QB_MAIN
4 - SEL$5481112B
5 - SEL$5481112B / T2@QB_MIDDLE
6 - SEL$5481112B / T2@QB_MIDDLE
7 - SEL$7B95B515 / V2@QB_MIDDLE
8 - SEL$7B95B515
9 - SEL$7B95B515 / T3@QB_INNER
10 - SEL$7B95B515 / T3@QB_INNER
11 - SEL$7B95B515 / T4@QB_INNER
------------------------------------------------------------
Predicate Information:
----------------------
6 - access("T2"."N2"="T1"."N1")
10 - access("T3"."N2"="T2"."N1")
11 - access("T4"."N1"="T3"."N1")In conclusion, the goal of Join Predicate Push Down Additional Phase is to combine several JPPD-transformed QBs even when the optimizer decided not to use them when it considereded them separately.
