14 Replies Latest reply on Mar 8, 2017 9:32 AM by Hesh

    Inline query join

    Hesh

      Hi,

       

      I got a strange problem with my query performance.

      I have a query like following.

       

      select c1,c2 from

                               (select c1 from t1,t2,t3

                                    where t1.c2=t2.c2

                                     and t2.c3=t3.c3

                             )tab1, t4,t5,t6

      wehre tab1.c1=t4.c4 and t4.c4=t5.c5

       

      This query regularly completed in 5 min occasionally takes long like 15 min like today.

      Strange thing is that the inline query tab1 has no records today!

      Not sure why no records are joining other tables and consuming 15 min of time!

       

      I have tried hint LEADING  but nothing changed.

      Appreciate your  suggestion.

       

      Thanks

      Hesh

        • 1. Re: Inline query join
          chris227

          I dont see any join predicate on t6 so probalby yoou have got a cartesian product.

          • 2. Re: Inline query join
            AndrewSayer

            Hesh wrote:

             

            Hi,

             

            I got a strange problem with my query performance.

            I have a query like following.

             

            select c1,c2 from

            (select c1 from t1,t2,t3

            where t1.c2=t2.c2

            and t2.c3=t3.c3

            )tab1, t4,t5,t6

            wehre tab1.c1=t4.c4 and t4.c4=t5.c5

             

            This query regularly completed in 5 min occasionally takes long like 15 min like today.

            Strange thing is that the inline query tab1 has no records today!

            Not sure why no records are joining other tables and consuming 15 min of time!

             

            I have tried hint LEADING but nothing changed.

            Appreciate your suggestion.

             

            Thanks

            Hesh

            Have you checked the actual execution plan that gets used? Is it as expected? Have you compared this to what happened before? Where does the work go?

             

            IMO trying hints without even looking at the plan is reckless. If you just used the word LEADING then of course nothing changed.

            • 3. Re: Inline query join
              Ankush Tiwari

              have you tried calculating subquery's execution time?
              first execute that and see if its working fine and secondly narrow down no of records giving join with t6 as well of main query.
              you can also use rownum(e.g rownum<500, rownum<400 and so on).

              • 4. Re: Inline query join
                Hesh

                Thanks Chris, Andrew & Ankush

                   

                    The no join to t6 is a typo, I was trying to replicate my actual query.

                    I have no earlier execution plan to compare the current one

                    One more thing to say about this is, when I execute the inner inline view it took only few seconds to execute.

                    And when I replace the inner inlie view with a 'select c1 from dual where 1=2' to make that as 'no' records query

                    then the query executed in couple of seconds.

                   

                    I believe something happening when we join the Sub query and the outer query.

                 

                Thanks

                Hesh

                • 5. Re: Inline query join
                  AndrewSayer

                  Hesh wrote:

                   

                  Thanks Chris, Andrew & Ankush

                   

                  The no join to t6 is a typo, I was trying to replicate my actual query.

                  I have no earlier execution plan to compare the current one

                  One more thing to say about this is, when I execute the inner inline view it took only few seconds to execute.

                  And when I replace the inner inlie view with a 'select c1 from dual where 1=2' to make that as 'no' records query

                  then the query executed in couple of seconds.

                   

                  I believe something happening when we join the Sub query and the outer query.

                   

                  Thanks

                  Hesh

                  I have no idea what is happening as you haven't shared any real information.

                   

                  When your query takes too long ...

                  What is the execution plan?

                  Is that expected?

                  What do you really expect to happen?

                  • 6. Re: Inline query join
                    Hesh

                    Andrew,

                     

                    When I run this query today the inner query returns no records and completed in couple of seconds.

                    What I wonder is when there are no records to join the inner zero records with outer query what is the outer query doing for 15 min?

                     

                     

                     

                    Plan hash value: 1660356609

                     

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

                    | Id  | Operation                                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

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

                    |   0 | SELECT STATEMENT                                    |                      |     1 |   268 | 99766   (1)| 00:00:08 |       |       |        |      |            |

                    |   1 |  PX COORDINATOR                                     |                      |       |       |            |          |       |       |        |      |            |

                    |   2 |   PX SEND QC (RANDOM)                               | :TQ10006             |     1 |   268 | 99766   (1)| 00:00:08 |       |       |  Q1,06 | P->S | QC (RAND)  |

                    |   3 |    NESTED LOOPS                                     |                      |     1 |   268 | 99766   (1)| 00:00:08 |       |       |  Q1,06 | PCWP |            |

                    |   4 |     NESTED LOOPS                                    |                      |     1 |   268 | 99766   (1)| 00:00:08 |       |       |  Q1,06 | PCWP |            |

                    |   5 |      NESTED LOOPS                                   |                      |     1 |   216 | 99765   (1)| 00:00:08 |       |       |  Q1,06 | PCWP |            |

                    |*  6 |       HASH JOIN                                     |                      |     1 |   175 | 99765   (1)| 00:00:08 |       |       |  Q1,06 | PCWP |            |

                    |   7 |        JOIN FILTER CREATE                           | :BF0000              |    16 |  2128 | 93954   (1)| 00:00:08 |       |       |  Q1,06 | PCWP |            |

                    |   8 |         PX RECEIVE                                  |                      |    16 |  2128 | 93954   (1)| 00:00:08 |       |       |  Q1,06 | PCWP |            |

                    |   9 |          PX SEND BROADCAST                          | :TQ10005             |    16 |  2128 | 93954   (1)| 00:00:08 |       |       |  Q1,05 | P->P | BROADCAST  |

                    |* 10 |           HASH JOIN                                 |                      |    16 |  2128 | 93954   (1)| 00:00:08 |       |       |  Q1,05 | PCWP |            |

                    |  11 |            JOIN FILTER CREATE                       | :BF0001              |     3 |   363 | 92253   (1)| 00:00:08 |       |       |  Q1,05 | PCWP |            |

                    |  12 |             PX RECEIVE                              |                      |     3 |   363 | 92253   (1)| 00:00:08 |       |       |  Q1,05 | PCWP |            |

                    |  13 |              PX SEND BROADCAST                      | :TQ10004             |     3 |   363 | 92253   (1)| 00:00:08 |       |       |  Q1,04 | P->P | BROADCAST  |

                    |* 14 |               HASH JOIN                             |                      |     3 |   363 | 92253   (1)| 00:00:08 |       |       |  Q1,04 | PCWP |            |

                    |  15 |                PART JOIN FILTER CREATE              | :BF0002              |     2 |    38 | 46301   (1)| 00:00:04 |       |       |  Q1,04 | PCWP |            |

                    |  16 |                 PX RECEIVE                          |                      |     2 |    38 | 46301   (1)| 00:00:04 |       |       |  Q1,04 | PCWP |            |

                    |  17 |                  PX SEND BROADCAST                  | :TQ10003             |     2 |    38 | 46301   (1)| 00:00:04 |       |       |  Q1,03 | P->P | BROADCAST  |

                    |  18 |                   VIEW                              |                      |     2 |    38 | 46301   (1)| 00:00:04 |       |       |  Q1,03 | PCWP |            |

                    |* 19 |                    FILTER                           |                      |       |       |            |          |       |       |  Q1,03 | PCWC |            |

                    |  20 |                     HASH GROUP BY                   |                      |     2 |   156 | 46301   (1)| 00:00:04 |       |       |  Q1,03 | PCWP |            |

                    |  21 |                      PX RECEIVE                     |                      |     2 |   156 | 46301   (1)| 00:00:04 |       |       |  Q1,03 | PCWP |            |

                    |  22 |                       PX SEND HASH                  | :TQ10002             |     2 |   156 | 46301   (1)| 00:00:04 |       |       |  Q1,02 | P->P | HASH       |

                    |  23 |                        HASH GROUP BY                |                      |     2 |   156 | 46301   (1)| 00:00:04 |       |       |  Q1,02 | PCWP |            |

                    |* 24 |                         HASH JOIN RIGHT SEMI        |                      |   154K|    11M| 46300   (1)| 00:00:04 |       |       |  Q1,02 | PCWP |            |

                    |  25 |                          PX RECEIVE                 |                      |   658K|  3857K|   343   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

                    |  26 |                           PX SEND HASH              | :TQ10000             |   658K|  3857K|   343   (0)| 00:00:01 |       |       |  Q1,00 | P->P | HASH       |

                    |  27 |                            PX BLOCK ITERATOR        |                      |   658K|  3857K|   343   (0)| 00:00:01 |       |       |  Q1,00 | PCWC |            |

                    |  28 |                             TABLE ACCESS FULL       | R_ITEM_SKU       |   658K|  3857K|   343   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |

                    |  29 |                          PX RECEIVE                 |                      |   154K|    10M| 45956   (1)| 00:00:04 |       |       |  Q1,02 | PCWP |            |

                    |  30 |                           PX SEND HASH              | :TQ10001             |   154K|    10M| 45956   (1)| 00:00:04 |       |       |  Q1,01 | P->P | HASH       |

                    |* 31 |                            HASH JOIN RIGHT SEMI     |                      |   154K|    10M| 45956   (1)| 00:00:04 |       |       |  Q1,01 | PCWP |            |

                    |  32 |                             JOIN FILTER CREATE      | :BF0004              |     1 |    21 |     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |

                    |  33 |                              PART JOIN FILTER CREATE| :BF0003              |     1 |    21 |     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |

                    |* 34 |                               TABLE ACCESS FULL     | L_P_TYPE             |     1 |    21 |     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |

                    |* 35 |                             HASH JOIN               |                      |  1080K|    52M| 45954   (1)| 00:00:04 |       |       |  Q1,01 | PCWP |            |

                    |  36 |                              JOIN FILTER CREATE     | :BF0005              |     3 |    48 |     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |

                    |* 37 |                               TABLE ACCESS FULL     | R_LOCATION     |     3 |    48 |     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |

                    |  38 |                              JOIN FILTER USE        | :BF0004               |   102M|  3409M| 45913   (1)| 00:00:04 |       |       |  Q1,01 | PCWP |            |

                    |  39 |                               JOIN FILTER USE       | :BF0005               |   102M|  3409M| 45913   (1)| 00:00:04 |       |       |  Q1,01 | PCWP |            |

                    |  40 |                                PX BLOCK ITERATOR    |                       |   102M|  3409M| 45913   (1)| 00:00:04 |     1 |    38 |  Q1,01 | PCWC |            |

                    |* 41 |                                 TABLE ACCESS FULL   | T_ITEM_PRICE   |   102M|  3409M| 45913   (1)| 00:00:04 |     1 |   190 |  Q1,01 | PCWP |            |

                    |  42 |                PX BLOCK ITERATOR                    |                       |   102M|  9935M| 45913   (1)| 00:00:04 |:BF0002|:BF0002|  Q1,04 | PCWC |            |

                    |* 43 |                 TABLE ACCESS FULL                   | T_ITEM_PRICE   |   102M|  9935M| 45913   (1)| 00:00:04 |   KEY |   KEY |  Q1,04 | PCWP |            |

                    |  44 |            JOIN FILTER USE                          | :BF0001               |   658K|  7711K|  1701   (1)| 00:00:01 |       |       |  Q1,05 | PCWP |            |

                    |  45 |             PX BLOCK ITERATOR                       |                       |   658K|  7711K|  1701   (1)| 00:00:01 |       |       |  Q1,05 | PCWC |            |

                    |* 46 |              TABLE ACCESS FULL                      | W_D_ITEM_SKU   |   658K|  7711K|  1701   (1)| 00:00:01 |       |       |  Q1,05 | PCWP |            |

                    |  47 |        JOIN FILTER USE                              | :BF0000               |   965K|    38M|  5810   (1)| 00:00:01 |       |       |  Q1,06 | PCWP |            |

                    |  48 |         PX BLOCK ITERATOR                           |                       |   965K|    38M|  5810   (1)| 00:00:01 |     1 |   204 |  Q1,06 | PCWC |            |

                    |* 49 |          TABLE ACCESS FULL                          | W_F_LN_TRANS       |   965K|    38M|  5810   (1)| 00:00:01 |     1 |   204 |  Q1,06 | PCWP |            |

                    |* 50 |       TABLE ACCESS BY INDEX ROWID                   | L_P_TYPE         |     1 |    41 |     0   (0)| 00:00:01 |       |       |  Q1,06 | PCWP |            |

                    |* 51 |        INDEX UNIQUE SCAN                            | L_P_TYPE_PK       |     1 |       |     0   (0)| 00:00:01 |       |       |  Q1,06 | PCWP |            |

                    |* 52 |      INDEX UNIQUE SCAN                              | R_LOCATION_PK |     1 |       |     0   (0)| 00:00:01 |       |       |  Q1,06 | PCWP |            |

                    |* 53 |     TABLE ACCESS BY INDEX ROWID                     | R_LOCATION     |     1 |    52 |     0   (0)| 00:00:01 |       |       |  Q1,06 | PCWP |            |

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

                     

                     

                     

                    Note

                    -----

                       - automatic DOP: Computed Degree of Parallelism is 8 because of degree limit

                       - parallel scans affinitized for buffer cache

                    • 7. Re: Inline query join
                      AndrewSayer

                      Is that expected?

                      What do you really expect to happen?

                       

                      I don't intend on reading the plan until those questions are answered, especially considering it's using parallelism which wasn't mentioned at all.

                      • 8. Re: Inline query join
                        Chris Hunt

                        I would try taking the subquery out altogether, this appears to be equivalent to the query you posted:

                        select c1,c2

                        from t1

                        inner join t2

                        on t2.c2 = t1.c2

                        inner join t3

                        on t3.c3=t2.c3

                        inner join  t4

                        on t4.c4 = t1.c1

                        inner join t5

                        on t5.c5=t4.c4

                        inner join t6

                        on 1=1   -- or whatever your join condition is

                        • 9. Re: Inline query join
                          Hesh

                          Andrew:- I would expect the query to be very quick as there are no records to join!

                          Chris:- Can we do anything without changing the code? like hints with join order?

                          • 10. Re: Inline query join
                            AndrewSayer

                            Hesh wrote:

                             

                            Andrew:- I would expect the query to be very quick as there are no records to join!

                            Chris:- Can we do anything without changing the code? like hints with join order?

                            Records? You mean rows?
                            And where? After what filters have been applied?

                             

                             

                            Does Oracle know this? Have you looked at the execution plan? Have you seen where it's driving the query from?

                             

                            Do you think the parallelism has helped Oracle choose this as a plan? What plan do YOU think should be used?

                            • 11. Re: Inline query join
                              Hesh

                              Hi Andrew,

                               

                              I have no clue whether optimizer know this! as we are joining a query out put not sure how to make it know? can you please suggest here?

                              when we join a table gather stats can help but when we join a result set, not sure how to do this.

                               

                              on parallelism , shall I use no parallel hint here?

                              • 12. Re: Inline query join
                                AndrewSayer

                                Hesh wrote:

                                 

                                Hi Andrew,

                                 

                                I have no clue whether optimizer know this! as we are joining a query out put not sure how to make it know? can you please suggest here?

                                when we join a table gather stats can help but when we join a result set, not sure how to do this.

                                 

                                on parallelism , shall I use no parallel hint here?

                                That's where reading the plan comes in, look at where the execution starts from and what it needs to do before it gets to a point where no rows exist in the result set.

                                Yes, stats play a role in join cardinalities (and cost) and therefore choice of execution path.

                                 

                                The parallelism is probably not needed, if you believe the statement should execute quick anyway then there's no reason to get it to coordinate slave sessions and prefer big multiblock reads of segments rather than index accesses. How has the parallelism come about? A no_parallel hint will do the trick at removing the parallelism from the plan.

                                 

                                Once youve done that, the plan will be much easier to follow. Share it with us using copy and paste from sql plus:

                                explain plan for select /*+no_parallel*/ blah...;

                                select * from table(dbms_xplan.display);

                                 

                                (when you copy the plan make sure you include the predicates and the notes section if they exist)

                                 

                                Try your best at reading the plan, check the predicates being applied at each step, how many rows are you likely to return from them? How much work would need to be done to do so? These may seem like awkward questions but they are exactly what you need to know and what you should be able to know - it's your data after all.

                                 

                                Once you've shared this information it'll be much easier to determine what probably should be done and why the optimizer has decided not to do it allowing us to see what we could change to get the desired behaviour.

                                • 13. Re: Inline query join
                                  Hesh

                                  Thanks Andrew,

                                   

                                  Here is the plan after no parallel hint. I have pasted all, looks very long text

                                   

                                  ex2.JPG

                                   

                                  Query Block Name / Object Alias (identified by operation id):

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

                                   

                                     1 - SEL$155B2D97

                                     8 - SEL$E2BB24CB / FOU_PRICE@SEL$3

                                     9 - SEL$E2BB24CB

                                    12 - SEL$E2BB24CB / RI@SEL$4

                                    16 - SEL$E2BB24CB / LPT1@SEL$5

                                    18 - SEL$E2BB24CB / LOC1@SEL$6

                                    21 - SEL$E2BB24CB / PRICE1@SEL$4

                                    22 - SEL$155B2D97 / DSKU@SEL$2

                                    24 - SEL$155B2D97 / PO@SEL$2

                                    27 - SEL$155B2D97 / PRICE@SEL$1

                                    28 - SEL$155B2D97 / LPT@SEL$9

                                    29 - SEL$155B2D97 / LPT@SEL$9

                                    30 - SEL$155B2D97 / LOC@SEL$10

                                    31 - SEL$155B2D97 / LOC@SEL$10

                                   

                                  Outline Data

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

                                   

                                    /*+

                                        BEGIN_OUTLINE_DATA

                                        PARTIAL_JOIN(@"SEL$E2BB24CB" "RI"@"SEL$4")

                                        USE_HASH_AGGREGATION(@"SEL$E2BB24CB")

                                        SWAP_JOIN_INPUTS(@"SEL$E2BB24CB" "RI"@"SEL$4")

                                        USE_HASH(@"SEL$E2BB24CB" "RI"@"SEL$4")

                                        USE_HASH(@"SEL$E2BB24CB" "PRICE1"@"SEL$4")

                                        USE_MERGE_CARTESIAN(@"SEL$E2BB24CB" "LOC1"@"SEL$6")

                                        LEADING(@"SEL$E2BB24CB" "LPT1"@"SEL$5" "LOC1"@"SEL$6" "PRICE1"@"SEL$4" "RI"@"SEL$4")

                                        FULL(@"SEL$E2BB24CB" "RI"@"SEL$4")

                                        FULL(@"SEL$E2BB24CB" "PRICE1"@"SEL$4")

                                        FULL(@"SEL$E2BB24CB" "LOC1"@"SEL$6")

                                        FULL(@"SEL$E2BB24CB" "LPT1"@"SEL$5")

                                        NLJ_BATCHING(@"SEL$155B2D97" "LOC"@"SEL$10")

                                        USE_NL(@"SEL$155B2D97" "LOC"@"SEL$10")

                                        USE_NL(@"SEL$155B2D97" "LPT"@"SEL$9")

                                        USE_HASH(@"SEL$155B2D97" "PRICE"@"SEL$1")

                                        USE_HASH(@"SEL$155B2D97" "PO"@"SEL$2")

                                        USE_HASH(@"SEL$155B2D97" "DSKU"@"SEL$2")

                                        SUBQUERY_PRUNING(@"SEL$155B2D97" "PRICE"@"SEL$1" PARTITION SUBPARTITION)

                                        LEADING(@"SEL$155B2D97" "FOU_PRICE"@"SEL$3" "DSKU"@"SEL$2" "PO"@"SEL$2" "PRICE"@"SEL$1" "LPT"@"SEL$9" "LOC"@"SEL$10")

                                        INDEX(@"SEL$155B2D97" "LOC"@"SEL$10" ("R_LOCATION"."LOCATION_KEY"))

                                        INDEX_RS_ASC(@"SEL$155B2D97" "LPT"@"SEL$9" ("L_P_TYPE"."LOV_KEY"))

                                        FULL(@"SEL$155B2D97" "PRICE"@"SEL$1")

                                        FULL(@"SEL$155B2D97" "PO"@"SEL$2")

                                        FULL(@"SEL$155B2D97" "DSKU"@"SEL$2")

                                        NO_ACCESS(@"SEL$155B2D97" "FOU_PRICE"@"SEL$3")

                                        OUTLINE(@"SEL$2")

                                        OUTLINE(@"SEL$3")

                                        MERGE(@"SEL$2")

                                        OUTLINE(@"SEL$64EAE176")

                                        OUTLINE(@"SEL$8")

                                        MERGE(@"SEL$64EAE176")

                                        OUTLINE(@"SEL$1184B019")

                                        OUTLINE(@"SEL$1")

                                        MERGE(@"SEL$1184B019")

                                        OUTLINE(@"SEL$4EE104D3")

                                        OUTLINE(@"SEL$9")

                                        OUTLINE(@"SEL$4")

                                        OUTLINE(@"SEL$5")

                                        MERGE(@"SEL$4EE104D3")

                                        OUTLINE(@"SEL$B32F13B4")

                                        OUTLINE(@"SEL$10")

                                        MERGE(@"SEL$4")

                                        OUTLINE(@"SEL$9834E3F4")

                                        OUTLINE(@"SEL$6")

                                        MERGE(@"SEL$B32F13B4")

                                        OUTLINE(@"SEL$27F62455")

                                        OUTLINE(@"SEL$11")

                                        MERGE(@"SEL$9834E3F4")

                                        OUTLINE(@"SEL$025E71EF")

                                        OUTLINE(@"SEL$7")

                                        MERGE(@"SEL$27F62455")

                                        OUTLINE_LEAF(@"SEL$155B2D97")

                                        MERGE(@"SEL$025E71EF")

                                        OUTLINE_LEAF(@"SEL$E2BB24CB")

                                        ALL_ROWS

                                        OPT_PARAM('_fix_control' '17376322:0 20636003:1')

                                        OPT_PARAM('star_transformation_enabled' 'true')

                                        OPT_PARAM('optimizer_dynamic_sampling' 0)

                                        DB_VERSION('12.1.0.2')

                                        OPTIMIZER_FEATURES_ENABLE('12.1.0.2')

                                        IGNORE_OPTIM_EMBEDDED_HINTS

                                        END_OUTLINE_DATA

                                    */

                                   

                                  Predicate Information (identified by operation id):

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

                                   

                                     4 - access("PRICE"."FROM_DT"=TO_DATE("FOU_PRICE"."FROM_DT",'YYYYMMDD') AND

                                                "PRICE"."RETAIL_ITEM_KEY"="FOU_PRICE"."RETAIL_ITEM_KEY")

                                     6 - access("DSKU"."RETAIL_ITEM_SKU_KEY"="PO"."RETAIL_ITEM_SKU_KEY")

                                         filter("PO"."CAL_DATE_PO_KEY"<TO_NUMBER("FOU_PRICE"."FROM_DT"))

                                     7 - access("FOU_PRICE"."RETAIL_ITEM_KEY"="DSKU"."RETAIL_ITEM_FOU_KEY")

                                     9 - filter(TO_NUMBER(MIN(TO_CHAR(INTERNAL_FUNCTION("PRICE1"."FROM_DT"),'YYYYMMDD')))>0 AND

                                                TO_NUMBER(MIN(TO_CHAR(INTERNAL_FUNCTION("PRICE1"."FROM_DT"),'YYYYMMDD')))>0)

                                    11 - access("RI"."RETAIL_ITEM_KEY"="PRICE1"."RETAIL_ITEM_KEY")

                                    13 - access("LPT1"."LOV_KEY"="PRICE1"."PRICE_TYPE_LOV_KEY" AND "LOC1"."LOCATION_KEY"="PRICE1"."LOCATION_KEY")

                                    16 - filter("LPT1"."LOV_TEXT"='Initial Selling Price')

                                    18 - filter(UPPER("LOC1"."LOCATION_NAME")='UNITED KINGDOM')

                                    21 - filter("PRICE1"."RETAIL_ITEM_SKU_KEY"=(-1) AND "PRICE1"."RETAILER"='Wiggle')

                                    24 - filter("PO"."ISP" IS NULL AND "PO"."CAL_DATE_PO_KEY">0)

                                    27 - filter("PRICE"."RETAIL_ITEM_SKU_KEY"=(-1) AND "PRICE"."RETAILER"='Wiggle')

                                    28 - filter("LPT"."LOV_TEXT"='Initial Selling Price')

                                    29 - access("LPT"."LOV_KEY"="PRICE"."PRICE_TYPE_LOV_KEY")

                                    30 - access("LOC"."LOCATION_KEY"="PRICE"."LOCATION_KEY")

                                    31 - filter(UPPER("LOC"."LOCATION_NAME")='UNITED KINGDOM')

                                   

                                  Column Projection Information (identified by operation id):

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

                                   

                                     1 - (#keys=0) "PRICE"."PRICE_TYPE_LOV_KEY"[NUMBER,22], "LPT"."LOV_KEY"[NUMBER,22],

                                         "FOU_PRICE"."RETAIL_ITEM_KEY"[NUMBER,22], "PRICE"."RETAIL_ITEM_KEY"[NUMBER,22], "PRICE"."FROM_DT"[DATE,7],

                                         "PO"."CAL_DATE_DEL_KEY"[NUMBER,22], "FOU_PRICE"."FROM_DT"[VARCHAR2,8], "PO"."PO_ID"[VARCHAR2,400],

                                         "PO"."PO_LINE_ID"[VARCHAR2,400], "PO"."CAL_DATE_PO_KEY"[NUMBER,22], "PRICE"."RI_PRICE_KEY"[NUMBER,22],

                                         "PRICE"."RI_PRICE_DT"[DATE,7], "PRICE"."LOCATION_KEY"[NUMBER,22], "PRICE"."CURRENT_FLG"[CHARACTER,4],

                                         "PRICE"."RETAIL_ITEM_SKU_KEY"[NUMBER,22], "PRICE"."RETAILER"[VARCHAR2,100], "LPT"."MODIFIED_DT"[DATE,7],

                                         "PRICE"."PRICE_INC_TAX_IN_CORP_CCY"[NUMBER,22], "PRICE"."PRICE_EXCL_TAX_IN_CORP_CCY"[NUMBER,22],

                                         "PRICE"."CORP_CCY_KEY"[NUMBER,22], "PRICE"."PRICE_INC_TAX_IN_LCL_CCY"[NUMBER,22],

                                         "PRICE"."PRICE_EXCL_TX_IN_LCL_CCY"[NUMBER,22], "PRICE"."LCL_CCY_KEY"[NUMBER,22], "PRICE"."PRICING_EVT_KEY"[NUMBER,22],

                                         "PRICE"."MODIFIED_DT"[DATE,7], "PRICE"."TO_DT"[DATE,7], "LPT"."LOV_KEY"[NUMBER,22], "LPT"."SOURCE_ID"[VARCHAR2,400],

                                         "LPT"."LOV_TEXT"[VARCHAR2,400], "LPT"."CREATED_DT"[DATE,7], "LPT"."MODIFIED_DT"[DATE,7], "LPT"."SOURCE_ID"[VARCHAR2,400],

                                         "LPT"."LOV_TEXT"[VARCHAR2,400], "LPT"."CREATED_DT"[DATE,7], "LOC"."LOCATION_KEY"[NUMBER,22], "LOC"."SOURCE_ID"[VARCHAR2,400],

                                         "LOC"."LOCATION_NAME"[VARCHAR2,1020], "LOC"."CCY_KEY"[NUMBER,22], "LOC"."LOCATION_TYPE_LOV_KEY"[NUMBER,22],

                                         "LOC"."CREATED_DT"[DATE,7], "LOC"."MODIFIED_DT"[DATE,7], "LOC"."ISO2_CD"[VARCHAR2,8], "LOC"."ISO3_CD"[VARCHAR2,12],

                                         "LOC"."VAT_REGION_CD"[VARCHAR2,480], "LOC"."IS_MAIN_LOCATION_FLG"[VARCHAR2,12]

                                     2 - (#keys=0) "PRICE"."PRICE_TYPE_LOV_KEY"[NUMBER,22], "LPT"."LOV_KEY"[NUMBER,22],

                                         "FOU_PRICE"."RETAIL_ITEM_KEY"[NUMBER,22], "PRICE"."RETAIL_ITEM_KEY"[NUMBER,22], "PRICE"."FROM_DT"[DATE,7],

                                         "PO"."CAL_DATE_DEL_KEY"[NUMBER,22], "FOU_PRICE"."FROM_DT"[VARCHAR2,8], "PO"."PO_ID"[VARCHAR2,400],

                                         "PO"."PO_LINE_ID"[VARCHAR2,400], "PO"."CAL_DATE_PO_KEY"[NUMBER,22], "PRICE"."RI_PRICE_KEY"[NUMBER,22],

                                         "PRICE"."RI_PRICE_DT"[DATE,7], "PRICE"."LOCATION_KEY"[NUMBER,22], "PRICE"."CURRENT_FLG"[CHARACTER,4],

                                         "PRICE"."RETAIL_ITEM_SKU_KEY"[NUMBER,22], "PRICE"."RETAILER"[VARCHAR2,100], "LPT"."MODIFIED_DT"[DATE,7],

                                         "PRICE"."PRICE_INC_TAX_IN_CORP_CCY"[NUMBER,22], "PRICE"."PRICE_EXCL_TAX_IN_CORP_CCY"[NUMBER,22],

                                         "PRICE"."CORP_CCY_KEY"[NUMBER,22], "PRICE"."PRICE_INC_TAX_IN_LCL_CCY"[NUMBER,22],

                                         "PRICE"."PRICE_EXCL_TX_IN_LCL_CCY"[NUMBER,22], "PRICE"."LCL_CCY_KEY"[NUMBER,22], "PRICE"."PRICING_EVT_KEY"[NUMBER,22],

                                         "PRICE"."MODIFIED_DT"[DATE,7], "PRICE"."TO_DT"[DATE,7], "LPT"."LOV_KEY"[NUMBER,22], "LPT"."SOURCE_ID"[VARCHAR2,400],

                                         "LPT"."LOV_TEXT"[VARCHAR2,400], "LPT"."CREATED_DT"[DATE,7], "LPT"."MODIFIED_DT"[DATE,7], "LPT"."SOURCE_ID"[VARCHAR2,400],

                                         "LPT"."LOV_TEXT"[VARCHAR2,400], "LPT"."CREATED_DT"[DATE,7], "LOC".ROWID[ROWID,10], "LOC"."LOCATION_KEY"[NUMBER,22]

                                     3 - (#keys=0) "FOU_PRICE"."RETAIL_ITEM_KEY"[NUMBER,22], "PRICE"."RETAIL_ITEM_KEY"[NUMBER,22], "PRICE"."FROM_DT"[DATE,7],

                                         "PO"."CAL_DATE_DEL_KEY"[NUMBER,22], "FOU_PRICE"."FROM_DT"[VARCHAR2,8], "PO"."PO_ID"[VARCHAR2,400],

                                         "PO"."PO_LINE_ID"[VARCHAR2,400], "PO"."CAL_DATE_PO_KEY"[NUMBER,22], "PRICE"."RI_PRICE_KEY"[NUMBER,22],

                                         "PRICE"."RI_PRICE_DT"[DATE,7], "PRICE"."LOCATION_KEY"[NUMBER,22], "PRICE"."CURRENT_FLG"[CHARACTER,4],

                                         "PRICE"."RETAIL_ITEM_SKU_KEY"[NUMBER,22], "PRICE"."RETAILER"[VARCHAR2,100], "PRICE"."PRICE_TYPE_LOV_KEY"[NUMBER,22],

                                         "PRICE"."PRICE_INC_TAX_IN_CORP_CCY"[NUMBER,22], "PRICE"."PRICE_EXCL_TAX_IN_CORP_CCY"[NUMBER,22],

                                         "PRICE"."CORP_CCY_KEY"[NUMBER,22], "PRICE"."PRICE_INC_TAX_IN_LCL_CCY"[NUMBER,22],

                                         "PRICE"."PRICE_EXCL_TX_IN_LCL_CCY"[NUMBER,22], "PRICE"."LCL_CCY_KEY"[NUMBER,22], "PRICE"."PRICING_EVT_KEY"[NUMBER,22],

                                         "PRICE"."MODIFIED_DT"[DATE,7], "PRICE"."TO_DT"[DATE,7], "LPT"."LOV_KEY"[NUMBER,22], "LPT"."SOURCE_ID"[VARCHAR2,400],

                                         "LPT"."LOV_TEXT"[VARCHAR2,400], "LPT"."CREATED_DT"[DATE,7], "LPT"."MODIFIED_DT"[DATE,7]

                                     4 - (#keys=2) "FOU_PRICE"."RETAIL_ITEM_KEY"[NUMBER,22], "PRICE"."RETAIL_ITEM_KEY"[NUMBER,22], "PRICE"."FROM_DT"[DATE,7],

                                         "PO"."CAL_DATE_DEL_KEY"[NUMBER,22], "FOU_PRICE"."FROM_DT"[VARCHAR2,8], "PO"."PO_ID"[VARCHAR2,400],

                                         "PO"."PO_LINE_ID"[VARCHAR2,400], "PO"."CAL_DATE_PO_KEY"[NUMBER,22], "PRICE"."RI_PRICE_KEY"[NUMBER,22],

                                         "PRICE"."RI_PRICE_DT"[DATE,7], "PRICE"."LOCATION_KEY"[NUMBER,22], "PRICE"."CURRENT_FLG"[CHARACTER,4],

                                         "PRICE"."RETAIL_ITEM_SKU_KEY"[NUMBER,22], "PRICE"."RETAILER"[VARCHAR2,100], "PRICE"."PRICE_TYPE_LOV_KEY"[NUMBER,22],

                                         "PRICE"."PRICE_INC_TAX_IN_CORP_CCY"[NUMBER,22], "PRICE"."PRICE_EXCL_TAX_IN_CORP_CCY"[NUMBER,22],

                                         "PRICE"."CORP_CCY_KEY"[NUMBER,22], "PRICE"."PRICE_INC_TAX_IN_LCL_CCY"[NUMBER,22],

                                         "PRICE"."PRICE_EXCL_TX_IN_LCL_CCY"[NUMBER,22], "PRICE"."LCL_CCY_KEY"[NUMBER,22], "PRICE"."PRICING_EVT_KEY"[NUMBER,22],

                                         "PRICE"."MODIFIED_DT"[DATE,7], "PRICE"."TO_DT"[DATE,7]

                                     5 - TO_DATE("FOU_PRICE"."FROM_DT",'YYYYMMDD')[7], "FOU_PRICE"."RETAIL_ITEM_KEY"[NUMBER,22],

                                         TO_DATE("FOU_PRICE"."FROM_DT",'YYYYMMDD')[7], "PO"."CAL_DATE_DEL_KEY"[NUMBER,22], "FOU_PRICE"."FROM_DT"[VARCHAR2,8],

                                         "PO"."PO_ID"[VARCHAR2,400], "PO"."PO_LINE_ID"[VARCHAR2,400], "PO"."CAL_DATE_PO_KEY"[NUMBER,22]

                                     6 - (#keys=1) "FOU_PRICE"."RETAIL_ITEM_KEY"[NUMBER,22], "FOU_PRICE"."FROM_DT"[VARCHAR2,8], "PO"."PO_ID"[VARCHAR2,400],

                                         "PO"."PO_LINE_ID"[VARCHAR2,400], "PO"."CAL_DATE_PO_KEY"[NUMBER,22], "PO"."CAL_DATE_DEL_KEY"[NUMBER,22]

                                     7 - (#keys=1) "FOU_PRICE"."RETAIL_ITEM_KEY"[NUMBER,22], "FOU_PRICE"."FROM_DT"[VARCHAR2,8],

                                         "DSKU"."RETAIL_ITEM_SKU_KEY"[NUMBER,22]

                                     8 - "FOU_PRICE"."RETAIL_ITEM_KEY"[NUMBER,22], "FOU_PRICE"."FROM_DT"[VARCHAR2,8]

                                     9 - "PRICE1"."RETAIL_ITEM_KEY"[NUMBER,22], MIN(TO_CHAR(INTERNAL_FUNCTION("PRICE1"."FROM_DT"),'YYYYMMDD'))[8]

                                    10 - (#keys=2) "PRICE1"."RETAIL_ITEM_SKU_KEY"[NUMBER,22], "PRICE1"."RETAIL_ITEM_KEY"[NUMBER,22],

                                         MIN(TO_CHAR(INTERNAL_FUNCTION("PRICE1"."FROM_DT"),'YYYYMMDD'))[8]

                                    11 - (#keys=1) "PRICE1"."RETAIL_ITEM_KEY"[NUMBER,22], "PRICE1"."FROM_DT"[DATE,7], "PRICE1"."RETAIL_ITEM_SKU_KEY"[NUMBER,22]

                                    12 - "RI"."RETAIL_ITEM_KEY"[NUMBER,22]

                                    13 - (#keys=2) "PRICE1"."FROM_DT"[DATE,7], "PRICE1"."RETAIL_ITEM_KEY"[NUMBER,22], "PRICE1"."RETAIL_ITEM_SKU_KEY"[NUMBER,22]

                                    14 - "LPT1"."LOV_KEY"[NUMBER,22], "LOC1"."LOCATION_KEY"[NUMBER,22], "LPT1"."LOV_KEY"[NUMBER,22]

                                    15 - (#keys=0) "LPT1"."LOV_KEY"[NUMBER,22], "LOC1"."LOCATION_KEY"[NUMBER,22]

                                    16 - "LPT1"."LOV_KEY"[NUMBER,22]

                                    17 - (#keys=0) "LOC1"."LOCATION_KEY"[NUMBER,22]

                                    18 - "LOC1"."LOCATION_KEY"[NUMBER,22]

                                    19 - (rowset=200) "PRICE1"."LOCATION_KEY"[NUMBER,22], "PRICE1"."RETAIL_ITEM_KEY"[NUMBER,22],

                                         "PRICE1"."RETAIL_ITEM_SKU_KEY"[NUMBER,22], "PRICE1"."PRICE_TYPE_LOV_KEY"[NUMBER,22], "PRICE1"."FROM_DT"[DATE,7]

                                    20 - (rowset=200) "PRICE1"."LOCATION_KEY"[NUMBER,22], "PRICE1"."RETAIL_ITEM_KEY"[NUMBER,22],

                                         "PRICE1"."RETAIL_ITEM_SKU_KEY"[NUMBER,22], "PRICE1"."PRICE_TYPE_LOV_KEY"[NUMBER,22], "PRICE1"."FROM_DT"[DATE,7]

                                    21 - (rowset=200) "PRICE1"."LOCATION_KEY"[NUMBER,22], "PRICE1"."RETAIL_ITEM_KEY"[NUMBER,22],

                                         "PRICE1"."RETAIL_ITEM_SKU_KEY"[NUMBER,22], "PRICE1"."PRICE_TYPE_LOV_KEY"[NUMBER,22], "PRICE1"."FROM_DT"[DATE,7]

                                    22 - (rowset=200) "DSKU"."RETAIL_ITEM_SKU_KEY"[NUMBER,22], "DSKU"."RETAIL_ITEM_FOU_KEY"[NUMBER,22]

                                    23 - (rowset=151) "PO"."PO_ID"[VARCHAR2,400], "PO"."PO_LINE_ID"[VARCHAR2,400], "PO"."CAL_DATE_PO_KEY"[NUMBER,22],

                                         "PO"."CAL_DATE_DEL_KEY"[NUMBER,22], "PO"."RETAIL_ITEM_SKU_KEY"[NUMBER,22]

                                    24 - (rowset=151) "PO"."PO_ID"[VARCHAR2,400], "PO"."PO_LINE_ID"[VARCHAR2,400], "PO"."CAL_DATE_PO_KEY"[NUMBER,22],

                                         "PO"."CAL_DATE_DEL_KEY"[NUMBER,22], "PO"."RETAIL_ITEM_SKU_KEY"[NUMBER,22]

                                    25 - (rowset=200) "PRICE"."RI_PRICE_KEY"[NUMBER,22], "PRICE"."RI_PRICE_DT"[DATE,7], "PRICE"."LOCATION_KEY"[NUMBER,22],

                                         "PRICE"."RETAIL_ITEM_KEY"[NUMBER,22], "PRICE"."RETAIL_ITEM_SKU_KEY"[NUMBER,22], "PRICE"."RETAILER"[VARCHAR2,100],

                                         "PRICE"."PRICE_TYPE_LOV_KEY"[NUMBER,22], "PRICE"."PRICE_INC_TAX_IN_CORP_CCY"[NUMBER,22],

                                         "PRICE"."PRICE_EXCL_TAX_IN_CORP_CCY"[NUMBER,22], "PRICE"."CORP_CCY_KEY"[NUMBER,22],

                                         "PRICE"."PRICE_INC_TAX_IN_LCL_CCY"[NUMBER,22], "PRICE"."PRICE_EXCL_TX_IN_LCL_CCY"[NUMBER,22],

                                         "PRICE"."LCL_CCY_KEY"[NUMBER,22], "PRICE"."PRICING_EVT_KEY"[NUMBER,22], "PRICE"."MODIFIED_DT"[DATE,7],

                                         "PRICE"."FROM_DT"[DATE,7], "PRICE"."TO_DT"[DATE,7], "PRICE"."CURRENT_FLG"[CHARACTER,4]

                                    26 - (rowset=200) "PRICE"."RI_PRICE_KEY"[NUMBER,22], "PRICE"."RI_PRICE_DT"[DATE,7], "PRICE"."LOCATION_KEY"[NUMBER,22],

                                         "PRICE"."RETAIL_ITEM_KEY"[NUMBER,22], "PRICE"."RETAIL_ITEM_SKU_KEY"[NUMBER,22], "PRICE"."RETAILER"[VARCHAR2,100],

                                         "PRICE"."PRICE_TYPE_LOV_KEY"[NUMBER,22], "PRICE"."PRICE_INC_TAX_IN_CORP_CCY"[NUMBER,22],

                                         "PRICE"."PRICE_EXCL_TAX_IN_CORP_CCY"[NUMBER,22], "PRICE"."CORP_CCY_KEY"[NUMBER,22],

                                         "PRICE"."PRICE_INC_TAX_IN_LCL_CCY"[NUMBER,22], "PRICE"."PRICE_EXCL_TX_IN_LCL_CCY"[NUMBER,22],

                                         "PRICE"."LCL_CCY_KEY"[NUMBER,22], "PRICE"."PRICING_EVT_KEY"[NUMBER,22], "PRICE"."MODIFIED_DT"[DATE,7],

                                         "PRICE"."FROM_DT"[DATE,7], "PRICE"."TO_DT"[DATE,7], "PRICE"."CURRENT_FLG"[CHARACTER,4]

                                    27 - (rowset=200) "PRICE"."RI_PRICE_KEY"[NUMBER,22], "PRICE"."RI_PRICE_DT"[DATE,7], "PRICE"."LOCATION_KEY"[NUMBER,22],

                                         "PRICE"."RETAIL_ITEM_KEY"[NUMBER,22], "PRICE"."RETAIL_ITEM_SKU_KEY"[NUMBER,22], "PRICE"."RETAILER"[VARCHAR2,100],

                                         "PRICE"."PRICE_TYPE_LOV_KEY"[NUMBER,22], "PRICE"."PRICE_INC_TAX_IN_CORP_CCY"[NUMBER,22],

                                         "PRICE"."PRICE_EXCL_TAX_IN_CORP_CCY"[NUMBER,22], "PRICE"."CORP_CCY_KEY"[NUMBER,22],

                                         "PRICE"."PRICE_INC_TAX_IN_LCL_CCY"[NUMBER,22], "PRICE"."PRICE_EXCL_TX_IN_LCL_CCY"[NUMBER,22],

                                         "PRICE"."LCL_CCY_KEY"[NUMBER,22], "PRICE"."PRICING_EVT_KEY"[NUMBER,22], "PRICE"."MODIFIED_DT"[DATE,7],

                                         "PRICE"."FROM_DT"[DATE,7], "PRICE"."TO_DT"[DATE,7], "PRICE"."CURRENT_FLG"[CHARACTER,4]

                                    28 - "LPT"."LOV_KEY"[NUMBER,22], "LPT"."SOURCE_ID"[VARCHAR2,400], "LPT"."LOV_TEXT"[VARCHAR2,400],

                                         "LPT"."CREATED_DT"[DATE,7], "LPT"."MODIFIED_DT"[DATE,7]

                                    29 - "LPT".ROWID[ROWID,10], "LPT"."LOV_KEY"[NUMBER,22]

                                    30 - "LOC".ROWID[ROWID,10], "LOC"."LOCATION_KEY"[NUMBER,22]

                                    31 - "LOC"."SOURCE_ID"[VARCHAR2,400], "LOC"."LOCATION_NAME"[VARCHAR2,1020], "LOC"."CCY_KEY"[NUMBER,22],

                                         "LOC"."LOCATION_TYPE_LOV_KEY"[NUMBER,22], "LOC"."CREATED_DT"[DATE,7], "LOC"."MODIFIED_DT"[DATE,7],

                                         "LOC"."ISO2_CD"[VARCHAR2,8], "LOC"."ISO3_CD"[VARCHAR2,12], "LOC"."VAT_REGION_CD"[VARCHAR2,480],

                                         "LOC"."IS_MAIN_LOCATION_FLG"[VARCHAR2,12]

                                   

                                  Sql Plan Directive information:

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

                                   

                                    Valid directive ids:

                                      13318700703581436900

                                      15646458456550078436

                                      9481313164973019197

                                      15938605892328952775

                                      10339430590159371381

                                      5411664090857378686

                                      5517281529539311123

                                      12913857529418151512

                                      6694834864838713253

                                      10902403729885830769

                                      5982549220935226775

                                      11896269771632049815

                                      14037252734412583272

                                   

                                   

                                  Note

                                  -----

                                     - Degree of Parallelism is 1 because of hint

                                     - this is an adaptive plan

                                  • 14. Re: Inline query join
                                    Hesh

                                    Hi Andrew,

                                     

                                    Looks like no parallel hint is working for this!

                                     

                                    Many Thanks

                                    Hesh