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.<\/p>\n
I’ll be using the model consisting of the following four tables:<\/p>\n
create table t1 \n(\n n1 number,\n n2 number \n) ;\n\ninsert into t1 select level,level from dual connect by level <= 1 ;\n\nexec dbms_stats.gather_table_stats(null,'t1') ;\n\ncreate table t2\n(\n n1 number not null,\n n2 number\n) ;\n\ninsert into t2 select level,level from dual connect by level <= 400000 ;\n\ncreate index ix_t2_1 on t2 (n2) ;\n\ncreate unique index pk_t2 on t2 (n1) ;\n\nexec dbms_stats.gather_table_stats(null,'t2', cascade => true ) ;\n\ncreate table t3\n(\n n1 number,\n n2 number\n) ;\n\ninsert into t3 select level, level from dual connect by level <= 1100000 ;\n\ncreate index ix_t3_1 on t3 (n2) ;\n\nexec dbms_stats.gather_table_stats(null,'t3', cascade => true ) ;\n\ncreate table t4\n(\n n1 number not null\n);\n\ninsert into t4 select level from dual connect by level <= 300000 ;\n\ncreate unique index pk_t4 on t4 (n1) ;\n\nexec dbms_stats.gather_table_stats(null,'t4', cascade => true ) ;<\/code><\/pre>\nLet’s take a closer look at the following query:<\/p>\n
select \/*+ qb_name(QB_MAIN) *\/\nt1.n2\n from t1 ,\n ( select \/*+ qb_name(QB_MIDDLE) *\/ t2.n2\n from \n t2 ,\n ( select \/*+ qb_name(QB_INNER ) *\/ t3.n2\n from t3, t4\n where t4.n1 = t3.n1\n ) v2\n where v2.n2(+) = t2.n1\n ) v1\n where v1.n2(+) = t1.n1 ;<\/code><\/pre>\nBesides 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.<\/p>\n
The optimizer first considered, but rejected the JPPD from QB_MIDDLE to QB_INNER:<\/p>\n
2403 JPPD: Not update best state, Cost = 997.835318\n2404 JPPD: Will not use JPPD from query block QB_MIDDLE (#2)\n<\/code><\/pre>\nThen it considered the JPPD from QB_MAIN to QB_MIDDLE:<\/p>\n
2407 JPPD: Checking validity of push-down from query block QB_MAIN (#1) to query block QB_MIDDLE (#2))<\/code><\/pre>\nFinally, it decided to do the JPPD from QB_MAIN to QB_MIDDLE:<\/p>\n
3663 JPPD: Will use JPPD from QB_MAIN (#1) to QB_MIDDLE (#2).<\/code><\/pre>\nIt’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:<\/p>\n
2406 JPPD: Checking validity of push-down in query block QB_MAIN (#1)\n2407 JPPD: Checking validity of push-down from query block QB_MAIN (#1) to query block QB_MIDDLE (#2)\n2408 Check Basic Validity for Non-Union View for query block QB_MIDDLE (#2)\n2409 JPPD: Passed validity checks\n2410 JPPD: JPPD: Pushdown from query block QB_MAIN (#1) passed validity checks.\n2411 Join-Predicate push-down on query block QB_MAIN (#1)\n2412 JPPD: Using search type: linear\n2413 JPPD: Considering join predicate push-down\n2414 JPPD: Starting iteration 1, state space = (2) : (0)\n2415 JPPD: Performing join predicate push-down (no transformation phase) from query block QB_MAIN (#1) to query block QB_MIDDLE (#2)\n...\n3022 Final cost for query block QB_MIDDLE (#2) - All Rows Plan:\n3023 Best join order: 1\n3024 Cost: 997.835318 Degree: 1 Card: 400000.000000 Bytes: 9200000.000000\n3025 Resc: 997.835318 Resc_io: 946.000000 Resc_cpu: 2491430510\n...\n3218 JPPD: Updated best state, Cost = 1002.687203\n3219 JPPD: Starting iteration 2, state space = (2) : (1)\n3220 JPPD: Performing join predicate push-down (candidate phase) from query block QB_MAIN (#1) to query block QB_MIDDLE (#2)\n3221 JPPD: Pushing predicate \"V1\".\"N2\"(+)=\"T1\".\"N1\"\n3222 from query block QB_MAIN (#1) to query block QB_MIDDLE (#2)\n3223 JPPD: Push dest of pred 0x869e2740 is qb 0x869d6450:query block QB_MIDDLE (#2)<\/code><\/pre>\nWhen 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.<\/p>\n
The total cost of such a transformed query is 362.206376:<\/p>\n
3649 Final cost for query block QB_MAIN (#1) - All Rows Plan:\n3650 Best join order: 1\n3651 Cost: 362.206376 Degree: 1 Card: 1.000000 Bytes: 8.000000<\/code><\/pre>\nIn 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.<\/p>\n
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.<\/p>\n
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:<\/p>\n
3665 CBQT Join Predicate Push Down Additional Phase\n3666 JPPD: Checking validity of push-down in query block QB_MIDDLE (#2)\n3667 JPPD: JPPD: Pushdown from query block QB_MIDDLE (#2) passed validity checks.\n3668 JPPD: Performing join predicate push-down (final phase) from query block QB_MIDDLE (#2) to query block QB_INNER (#3)\n3669 JPPD: Pushing predicate \"V2\".\"N2\"(+)=\"T2\".\"N1\"\n3670 from query block QB_MIDDLE (#2) to query block QB_INNER (#3)\n3671 JPPD: Push dest of pred 0xc16b1868 is qb 0x89c84958:query block QB_INNER (#3)\n3672 \n3673 \n3674 JPPD: Checking validity of push-down in query block QB_MAIN (#1)\n3675 JPPD: JPPD: Pushdown from query block QB_MAIN (#1) passed validity checks.\n3676 JPPD: Performing join predicate push-down (final phase) from query block QB_MAIN (#1) to query block QB_MIDDLE (#2)\n3677 JPPD: Pushing predicate \"V1\".\"N2\"(+)=\"T1\".\"N1\"\n3678 from query block QB_MAIN (#1) to query block QB_MIDDLE (#2)\n3679 JPPD: Push dest of pred 0xc16b4500 is qb 0x89c84458:query block QB_MIDDLE (#2)<\/code><\/pre>\nAs we can see, the optimizer reverted its previous decision not to do the JPPD from QB_MIDDLE to QB_INNER.<\/p>\n
It turns out that this combination yields the best execution plan:<\/p>\n
5056 Final cost for query block QB_MAIN (#1) - All Rows Plan:\n5057 Best join order: 1\n5058 Cost: 8.002284 Degree: 1 Card: 1.000000 Bytes: 8.000000\n<\/code><\/pre>\n----- Plan Table -----\n \n============\nPlan Table\n============\n----------------------------------------------------+-----------------------------------+\n| Id | Operation | Name | Rows | Bytes | Cost | Time |\n----------------------------------------------------+-----------------------------------+\n| 0 | SELECT STATEMENT | | | | 8 | | \n| 1 | NESTED LOOPS OUTER | | 1 | 8 | 8 | 00:00:01 |\n| 2 | TABLE ACCESS FULL | T1 | 1 | 6 | 4 | 00:00:01 |\n| 3 | VIEW PUSHED PREDICATE | | 1 | 2 | 4 | 00:00:01 |\n| 4 | NESTED LOOPS OUTER | | 1 | 12 | 4 | 00:00:01 |\n| 5 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 10 | 2 | 00:00:01 |\n| 6 | INDEX RANGE SCAN | IX_T2_1 | 1 | | 1 | 00:00:01 |\n| 7 | VIEW PUSHED PREDICATE | | 1 | 2 | 2 | 00:00:01 |\n| 8 | NESTED LOOPS | | 1 | 15 | 2 | 00:00:01 |\n| 9 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 10 | 2 | 00:00:01 |\n| 10 | INDEX RANGE SCAN | IX_T3_1 | 1 | | 1 | 00:00:01 |\n| 11 | INDEX UNIQUE SCAN | PK_T4 | 1 | 5 | 0 | | \n----------------------------------------------------+-----------------------------------+\nQuery Block Name \/ Object Alias(identified by operation id):\n------------------------------------------------------------\n 1 - QB_MAIN \n 2 - QB_MAIN \/ T1@QB_MAIN\n 3 - SEL$5481112B \/ V1@QB_MAIN\n 4 - SEL$5481112B \n 5 - SEL$5481112B \/ T2@QB_MIDDLE\n 6 - SEL$5481112B \/ T2@QB_MIDDLE\n 7 - SEL$7B95B515 \/ V2@QB_MIDDLE\n 8 - SEL$7B95B515 \n 9 - SEL$7B95B515 \/ T3@QB_INNER\n10 - SEL$7B95B515 \/ T3@QB_INNER\n11 - SEL$7B95B515 \/ T4@QB_INNER\n------------------------------------------------------------\nPredicate Information:\n----------------------\n6 - access(\"T2\".\"N2\"=\"T1\".\"N1\")\n10 - access(\"T3\".\"N2\"=\"T2\".\"N1\")\n11 - access(\"T4\".\"N1\"=\"T3\".\"N1\")<\/code><\/pre>\nIn 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
Analyzing Join Predicate Push Down Additional Phase by using the optimizer trace Continue Reading