10 Replies Latest reply on Aug 7, 2017 6:06 AM by chathu

    query including left outer join with two materialized views taking long time run?

    chathu

      Hi All,

       

      I have been trying to execute the following query which is involved with two materialized views and one permanent table. But its taking too much time to and I couldn't see the result count even after 4 or 5 hours. So I have used explain plan method to generate execution plan and noticed that huge number of records been generated on left outer join but main materialized view only contains around 29 million records though. So what can be the reason for generating lot of records more than expected?

       

      database version information

       

      Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

      PL/SQL Release 11.2.0.4.0 - Production

       

      sql query as follows

       

      SELECT COUNT(*)

      FROM ((MVW_VOUCHER_2017 V INNER JOIN ITEM IT ON V.itemuid = IT.PUID)

      LEFT OUTER JOIN MVW_DISTRIBUTOR_STOCKTARNSFER MT ON

      ( V.STARTSERIALNUMBER between MT.serialnumber AND MT.serialnumber2 ) AND V.ITEMUID = MT.ITEMUID );

       

      number of rows as follows

       

      materialized views

       

      MVW_VOUCHER_2017 - 29,180,865      [ non unique index on STARTSERIALNUMBER NUMBER(19)  and non unique index on ITEMUID NUMBER(10)]

      MVW_DISTRIBUTOR_STOCKTARNSFER - 53,902      [non-unique index on ( SERIALNUMBER NUMBER(19), SERIALNUMBER2 NUMBER(19), ITEMUID NUMBER(10) ) ]

       

      table

       

      ITEM - 179  [index on PUID column NUMBER(10) ]

       

       

      Explain plan output for above query

       

      Plan hash value: 3233610204

       

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

      | Id  | Operation                                       | Name                                                  | Rows       | Bytes        | Cost (%CPU)       | Time           |

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

      |   0 | SELECT STATEMENT                   |                                                            |     1         |    34           |  1359K (97)          | 04:31:52      |

      |   1 |  SORT AGGREGATE                     |                                                            |     1         |    34           |                              |                     |

      |*  2 |   HASH JOIN RIGHT OUTER        |                                                            |   118G     |  3762G      |  1359K (97)          | 04:31:52      |

      |   3 |    INDEX FAST FULL SCAN           | IX_SERIALRNG_DSTRANSFER      | 53902     |  1000K       |       61   (0)            | 00:00:01      |

      |*  4 |    HASH JOIN                                 |                                                            |    29M     |   417M       | 42824   (1)            | 00:08:34      |

      |   5 |     INDEX FULL SCAN                    | PK_ITEM                                           |   179       |  716            |     1   (0)               | 00:00:01       |

      |   6 |     MAT_VIEW ACCESS FULL        | MVW_VOUCHER_2017                    |    29M     |   306M       | 42742   (1)            | 00:08:33       |

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

       

      Predicate Information (identified by operation id):

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

       

         2 - access("V"."ITEMUID"="MT"."ITEMUID"(+))

             filter("V"."STARTSERIALNUMBER"<="MT"."SERIALNUMBER2"(+) AND

                    "V"."STARTSERIALNUMBER">="MT"."SERIALNUMBER"(+))

         4 - access("V"."ITEMUID"="IT"."PUID")

       

       

      Regards

      Chathura

        • 2. Re: query including left outer join with two materialized views taking long time run?
          chathu

          Hi Gaz,

           

          Thank you for directing me to above informative links. As per the article I would only have database version, sql query and explain plan table output because some privileges are restricted by client. By the way according to the docs \ tag could be used to preserve formatting but I couldn't get the expected result. Can you give some tip on how to do the formatting ?

           

          Thank you

          Chathu

          • 3. Re: query including left outer join with two materialized views taking long time run?
            Paulzip

            You have no filters on the data in question.  So you are hashing and joining 29 Million rows with 53902 rows with 179 rows.  Can you not see that is a lot of hashing and comparison operations?!

             

            What exactly are you counting?  Are you trying to count how many records are in MVW_VOUCHER_2017 with a certain criteria?  Counting involving left joins seems a bit weird.

            1 person found this helpful
            • 4. Re: query including left outer join with two materialized views taking long time run?

              I have been trying to execute the following query which is involved with two materialized views and one permanent table.

              . . .

              SELECT COUNT(*)

              FROM

              The 'solution' is REALLY simple: DON'T DO THAT!

               

              Except in rare cases a simple 'count' query is pretty much useless.

               

              What will you do differently if the result is 276184 than if the result was 49725?

               

              I fail to see how knowing ONLY the count will be of any help in planning your next steps.

               

              Why don't you start over and tell us: WHAT BUSINESS PROBLEM are you trying to solve?

               

              And why do you think a simple COUNT(*) will solve it?

              1 person found this helpful
              • 5. Re: query including left outer join with two materialized views taking long time run?
                chathu

                Hi All,

                 

                Thank you for all responses. My requirement is to get all voucher stock with information it's transfer from distributor warehouse. Voucher may be still in warehouse or transferred from so hence left outer join.  Even with selection of specific columns (eg: all rows from MVW_VOUCHER_2017 and name,transfer date from  MVW_DISTRIBUTOR_STOCKTARNSFER) execution plan contains same operations 2 - 6.

                 

                Regards

                Chathura

                • 6. Re: query including left outer join with two materialized views taking long time run?
                  Paulzip

                  chathu wrote:

                   

                  Hi All,

                   

                  Thank you for all responses. My requirement is to get all voucher stock with information it's transfer from distributor warehouse. Voucher may be still in warehouse or transferred from so hence left outer join. Even with selection of specific columns (eg: all rows from MVW_VOUCHER_2017 and name,transfer date from MVW_DISTRIBUTOR_STOCKTARNSFER) execution plan contains same operations 2 - 6.

                   

                  Regards

                  Chathura

                  Your requirement is to get more than 29 million rows from a query?  Why?!

                   

                  What is it you are doing that needs to return so much unfiltered data?

                  • 7. Re: query including left outer join with two materialized views taking long time run?
                    chathu

                    Hi ,

                     

                    This select statement is going to be used for creating another materialized view. So one can filter out by specific date or serial number etc.

                     

                    Regards

                    • 8. Re: query including left outer join with two materialized views taking long time run?
                      Paulzip

                      Why don't you just write a filtered query instead?

                       

                      The MView will be a large amount of data.

                      • 9. Re: query including left outer join with two materialized views taking long time run?
                        This select statement is going to be used for creating another materialized view. So one can filter out by specific date or serial number etc.

                         

                        Sorry - but I don't believe for a minute you plan to use a COUNT(*) query to create another materialized view.

                         

                        There is no point in posting a count query if you really need help with some other query - you may as well just mark the thread ANSWERED.

                         

                        Otherwise post the query you really need help with.

                        • 10. Re: query including left outer join with two materialized views taking long time run?
                          chathu

                          Hi All,

                           

                          Thank you for all responses. As suggested I'm posting SELECT statement and its execution plan below.

                           

                          SQL statement

                           

                          SELECT V.ITEMUID,

                                 V.STARTSERIALNUMBER,

                                 V.DISTRIBUTORUID,

                                 V.INVOICEDATE,

                                 V.GRNDATE,

                                 MT.NAME,

                                 MT.DS_TR_ID

                          FROM ((MVW_VOUCHER_2017 V INNER JOIN ITEM IT ON V.itemuid = IT.PUID)

                          LEFT OUTER JOIN MVW_DISTRIBUTOR_STOCKTARNSFER MT ON

                          ( V.STARTSERIALNUMBER BETWEEN MT.SERIALNUMBER AND MT.SERIALNUMBER2 ) AND V.ITEMUID = MT.ITEMUID );

                           

                          Explain plan table output

                           

                          EXPLAIN PLAN FOR

                          SELECT V.ITEMUID,

                                 V.STARTSERIALNUMBER,

                                 V.DISTRIBUTORUID,

                                 V.INVOICEDATE,

                                 V.GRNDATE,

                                 MT.NAME,

                                 MT.DS_TR_ID

                          FROM ((MVW_VOUCHER_2017 V INNER JOIN ITEM IT ON V.itemuid = IT.PUID)

                          LEFT OUTER JOIN MVW_DISTRIBUTOR_STOCKTARNSFER MT ON

                          ( V.STARTSERIALNUMBER BETWEEN MT.SERIALNUMBER AND MT.SERIALNUMBER2 ) AND V.ITEMUID = MT.ITEMUID );

                           

                          SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

                           

                          Plan hash value: 3403120843

                           

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

                          | Id  | Operation              | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |

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

                          |   0 | SELECT STATEMENT       |                               |   118G|    10T|  1420K (93)| 04:44:06 |

                          |*  1 |  HASH JOIN RIGHT OUTER |                               |   118G|    10T|  1420K (93)| 04:44:06 |

                          |   2 |   MAT_VIEW ACCESS FULL | MVW_DISTRIBUTOR_STOCKTARNSFER | 53902 |  3263K|   207   (1)| 00:00:03 |

                          |*  3 |   HASH JOIN            |                               |    29M|   890M| 42888   (1)| 00:08:35 |

                          |   4 |    INDEX FULL SCAN     | PK_ITEM                       |   179 |   716 |     1   (0)| 00:00:01 |

                          |   5 |    MAT_VIEW ACCESS FULL| MVW_VOUCHER_2017              |    29M|   779M| 42806   (1)| 00:08:34 |

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

                           

                          Predicate Information (identified by operation id):

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

                           

                             1 - access("V"."ITEMUID"="MT"."ITEMUID"(+))

                                 filter("V"."STARTSERIALNUMBER"<="MT"."SERIALNUMBER2"(+) AND

                                        "V"."STARTSERIALNUMBER">="MT"."SERIALNUMBER"(+))

                             3 - access("V"."ITEMUID"="IT"."PUID")

                           

                          Regards

                          Chathu