11 Replies Latest reply: Feb 21, 2013 12:31 PM by 991593 RSS

    hash join

    991593
      I have an index on CAT_MAP_ID column of STM_RPT_ITEM_PH6_MV but I don't know why it's not using nested look join for 21 rows returned (outer) to join 641k rows in STM_RPT_ITEM_PH6_MV table. I think that's the reason this query is consuming very high TEMP (87GB), can anyone help me explain this:
      ---------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                             | Name                        | E-Rows |  OMem |  1Mem | Used-Mem | Used-Tmp|
      ---------------------------------------------------------------------------------------------------------------------------
      |   0 | INSERT STATEMENT                      |                             |        |       |       |          |         |
      |   1 |  SORT GROUP BY                        |                             |      1 |   131M|  3585K|   90M (1)|     118K|
      |*  2 |   VIEW                                |                             |      1 |       |       |          |         |
      |   3 |    SORT UNIQUE                        |                             |      1 |   261M|  4982K|   90M (1)|         |
      |*  4 |     WINDOW SORT PUSHED RANK           |                             |      1 |   511M|  6878K|  172M (1)|         |
      |   5 |      NESTED LOOPS OUTER               |                             |      1 |       |       |          |         |
      |   6 |       NESTED LOOPS                    |                             |      1 |       |       |          |         |
      *|*  7 |        HASH JOIN                      |                             |      1 |  2047M|    75M|  470M (1)|      87M|*
      |   8 |         MAT_VIEW ACCESS BY INDEX ROWID| BSC_RPT_REBSUM_STRUCT_LI_MV |     21 |       |       |          |         |
      |   9 |          NESTED LOOPS                 |                             |   3960 |       |       |          |         |
      |  10 |           NESTED LOOPS                |                             |    185 |       |       |          |         |
      |* 11 |            HASH JOIN                  |                             |   2638 |  1465K|   902K| 4966K (0)|         |
      |* 12 |             HASH JOIN                 |                             |   4193 |   841K|   841K| 4992K (0)|         |
      |  13 |              TABLE ACCESS FULL        | BSC_RPT_REBSUM_TEMP         |   4193 |       |       |          |         |
      |  14 |              TABLE ACCESS FULL        | BSC_RPT_REBSUM_S1           |  83548 |       |       |          |         |
      |  15 |             MAT_VIEW ACCESS FULL      | BSC_RPT_REBSUM_MEM_MV       |  52555 |       |       |          |         |
      |* 16 |            TABLE ACCESS BY INDEX ROWID| MN_BUCKET_LINE              |      1 |       |       |          |         |
      |* 17 |             INDEX RANGE SCAN          | REBATEPAYMENTGRP            |   1460 |       |       |          |         |
      |* 18 |           INDEX RANGE SCAN            | BSC_RPT_STRUCT_LI_MV_IDX1   |     22 |       |       |          |         |
      |  19 |         MAT_VIEW ACCESS FULL          | BSC_RPT_ITEM_PH6_MV         *|    641K|*       |       |          |         |
      |* 20 |        INDEX UNIQUE SCAN              | MN_10291_PK                 |      1 |       |       |          |         |
      |  21 |       TABLE ACCESS BY INDEX ROWID     | BSC_SPLIT_PMT               |      1 |       |       |          |         |
      |* 22 |        INDEX RANGE SCAN               | BSC_904200_IDX1             |      1 |       |       |          |         |
      ---------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - filter("RPT"."RNK"=1)
         4 - filter(ROW_NUMBER() OVER ( PARTITION BY "BUCK_LI"."BUCKET_LINE_ID" ORDER BY
                    INTERNAL_FUNCTION("ITEM_VW"."BSC_PHLEVEL") DESC )<=1)
         7 - access("LIMV"."CAT_MAP_ID"="ITEM_VW"."CAT_MAP_ID" AND "ITEM_VW"."ITEM_ID"="BUCK_LI"."SALE_ITEM_ID")
        11 - access("MEM_MV"."PRC_PROGRAM_ID"="S1"."PRC_PROGRAM_ID" AND
                    "MEM_MV"."PARENT_MEMBER_ID"="S1"."MEMBER_ID_CUST")
        12 - access("TEMP"."CONTRACT_ID"="S1"."CONTRACT_ID" AND "TEMP"."REBATE_PMT_ID"="S1"."REBATE_PMT_ID")
        16 - filter(("BUCK_LI"."PMT_BENEFIT_ID" IS NOT NULL AND "BUCK_LI"."INCLUSION_TYPE"='INC' AND
                    "MEM_MV"."CHILD_MEMBER_ID"="BUCK_LI"."SALE_CONTRACTED_CUST_ID"))
        17 - access("TEMP"."REBATE_PMT_ID"="BUCK_LI"."REBATE_PMT_ID")
        18 - access("LIMV"."PRC_PROGRAM_ID"="S1"."PRC_PROGRAM_ID")
        20 - access("PRC"."PRC_PROGRAM_ID"="S1"."PRC_PROGRAM_ID")
        22 - access("TEMP"."REBATE_PMT_ID"="SPLIT"."REBATE_PMT_ID")
             filter("SPLIT"."REBATE_PMT_ID" IS NOT NULL)
      Note
      -----
      - dynamic sampling used for this statement
      - Warning: basic plan statistics not available. These are only collected when:
      * hint 'gather_plan_statistics' is used for the statement or
      * parameter 'statistics_level' is set to 'ALL', at session or system level

      Query Is:
      INSERT INTO MDK_TAB1
                  (sale_id, struct_doc_id, pdflg, member_id_cust, paid_end_date, payee, map_name, STM_phlevel, STM_phcode, STM_old_material_num, sold_to_cust_id, member_grp_name, 
                     prc_program_id, STM_r_af_type, rebate_pmt_id_num, rebate_pmt_id, sale_inv_qty, sales, earn_rebate_amt)
         SELECT   rpt.sale_id, rpt.struct_doc_id, rpt.pdflg, rpt.member_id_cust, rpt.paid_end_date, rpt.payee, rpt.map_name, rpt.STM_phlevel,
                  rpt.STM_phcode, rpt.STM_old_material_num, rpt.sold_to_cust_id, rpt.member_grp_name, rpt.prc_program_id, rpt.STM_r_af_type, rpt.rebate_pmt_id_num, 
                     rpt.rebate_pmt_id, rpt.sales_units, rpt.sales, SUM (rpt.earn_rebate_amt) earn_rebate_amt
             FROM (SELECT DISTINCT buck_li.sale_id, temp.contract_id struct_doc_id,
                                   temp.pdflg, s1.member_id_cust,
                                   temp.paid_end_date, SPLIT.payee,
                                   item_vw.map_name, item_vw.STM_phlevel,
                                   item_vw.STM_phcode, item_vw.STM_old_material_num,
                                   buck_li.sale_contracted_cust_id sold_to_cust_id,
                                   mem_mv.child_name member_grp_name, s1.prc_program_id, s1.STM_r_af_type,
                                   s1.rebate_pmt_id_num, s1.rebate_pmt_id,
                                   buck_li.sale_inv_qty AS sales_units,
                                   NULLIF (buck_li.sale_ext_amt, 0) sales,
                                   NULLIF
                                       (buck_li.STM_payment_amount, 0) earn_rebate_amt,
                                   ROW_NUMBER () OVER (PARTITION BY buck_li.bucket_line_id ORDER BY item_vw.STM_phlevel DESC)
                         rnk
                              FROM STM_rpt_rebsum_s1 s1,
                                   mn_bucket_line buck_li,
                                   STM_rpt_item_ph6_mv item_vw,
                                   STM_rpt_rebsum_struct_li_mv limv,
                                   STM_rpt_rebsum_mem_mv mem_mv,
                                   STM_rpt_rebsum_temp temp,
                                   STM_split_pmt SPLIT,
                                   mn_prc_program prc
                             WHERE temp.contract_id = s1.contract_id
                               AND temp.rebate_pmt_id = s1.rebate_pmt_id
                               AND temp.rebate_pmt_id = SPLIT.rebate_pmt_id(+)
                               AND temp.rebate_pmt_id = buck_li.rebate_pmt_id
                               AND limv.prc_program_id = s1.prc_program_id
                               AND prc.prc_program_id = s1.prc_program_id
                               AND mem_mv.prc_program_id = s1.prc_program_id
                               AND mem_mv.parent_member_id = s1.member_id_cust
                               AND *limv.cat_map_id = item_vw.cat_map_id*                         
                               AND item_vw.item_id = buck_li.sale_item_id
                               AND mem_mv.child_member_id = buck_li.sale_contracted_cust_id
                               AND buck_li.pmt_benefit_id IS NOT NULL
                               AND buck_li.inclusion_type = 'INC') rpt
            WHERE rpt.rnk = 1
         GROUP BY rpt.sale_id,
                  rpt.struct_doc_id,
                  rpt.pdflg,
                  rpt.member_id_cust,
                  rpt.paid_end_date,
                  rpt.payee,
                  rpt.map_name,
                  rpt.STM_phlevel,
                  rpt.STM_phcode,
                  rpt.STM_old_material_num,
                  rpt.sold_to_cust_id,
                  rpt.member_grp_name,
                  rpt.prc_program_id,
                  rpt.STM_r_af_type,
                  rpt.rebate_pmt_id_num,
                  rpt.rebate_pmt_id,
                  rpt.sales_units,
                  rpt.sales;
      Edited by: 988590 on Feb 21, 2013 10:33 AM
        • 1. Re: hash join
          Hemant K Chitale
          oops. wrong copy paste.

          Hemant K Chitale

          Edited by: Hemant K Chitale on Feb 18, 2013 11:20 AM
          • 2. Re: hash join
            Hemant K Chitale
            Can you post the execution plan and query in a formatted manner. Use the [ code ] and [ / code ] or { code } { code } tags (remove the spaces around the word code)
            See the FAQ at the top right

            Hemant K Chitale
            • 3. Re: hash join
              Nikolay Savvinov
              Hi,

              welcome to the forum!

              there are several problems with your approach to performance tuning:

              1) you focus on TEMP space usage, but ignore other, probably more significant factors such as I/O cost
              2) your attitude towards optimizer estimates is not consistent: you trust that the optimizer estimates are
              accurate enough so you don't find it necessary to supply any other information, however, you don't trust
              the optimizer to chose the best join method available based on its own estimates
              3) estimated temp space usage is 87M, not 87G
              4) did you try forcing the desired plan (nested loops) with hints? If yes, what happened? Can you provide
              some diagnostic information, including 4-digit Oracle version, plan with rowsource stats (from dbms_xplan.display_cursor,
              SQL trace or Real-time SQL monitor output)?

              Best regards,
              Nikolay
              • 4. Re: hash join
                rahulras
                Whats the Oracle version?
                Can you run query
                select column_name, num_distinct, num_nulls, num_buckets, sample_size, histogram
                from dba_tab_col_statistics 
                where table_name='BSC_RPT_ITEM_PH6_MV'; -- << use correct table/MV name here
                and post the output here?
                • 5. Re: hash join
                  991593
                  Thanks for the reply, I'll try to answer your queries:

                  1) I focus on TEMP usage because that's the problem at hand, I don't have IO or timing issues in this case.
                  2) I'm not sure I got you 100% here, what do you mean I don't trust optimizer
                  3) TEMP usage is 87GB, there is an issue with dbms xplan, read this:
                  http://www.antognini.ch/2009/05/wrong-information-about-temporary-space-usage/
                  4) Oracle version is 10.2.0.5.0, it's a vendor SQL, I've to get to the vendor to put SQL and get a release, I don't prefer putting hints to be honest.
                  • 6. Re: hash join
                    991593
                    Sorry for messing with the code thingy, it's the first timer's mistake, here is the formatted version:
                    ---------------------------------------------------------------------------------------------------------------------------
                    | Id  | Operation                             | Name                        | E-Rows |  OMem |  1Mem | Used-Mem | Used-Tmp|
                    ---------------------------------------------------------------------------------------------------------------------------
                    |   0 | INSERT STATEMENT                      |                             |        |       |       |          |         |
                    |   1 |  SORT GROUP BY                        |                             |      1 |   133M|  3616K|   90M (1)|     120K|
                    |*  2 |   VIEW                                |                             |      1 |       |       |          |         |
                    |   3 |    SORT UNIQUE                        |                             |      1 |   266M|  5026K|   90M (1)|         |
                    |*  4 |     WINDOW SORT PUSHED RANK           |                             |      1 |   520M|  6933K|  172M (1)|         |
                    |   5 |      NESTED LOOPS OUTER               |                             |      1 |       |       |          |         |
                    |   6 |       NESTED LOOPS                    |                             |      1 |       |       |          |         |
                    |*  7 |        HASH JOIN                      |                             |      1 |  2047M|   120M|  419M (1)|      93M|
                    |   8 |         MAT_VIEW ACCESS BY INDEX ROWID| BSC_RPT_REBSUM_STRUCT_LI_MV |     21 |       |       |          |         |
                    |   9 |          NESTED LOOPS                 |                             |   5468 |       |       |          |         |
                    |  10 |           NESTED LOOPS                |                             |    255 |       |       |          |         |
                    |* 11 |            HASH JOIN                  |                             |   2645 |  1465K|   902K| 3386K (0)|         |
                    |* 12 |             HASH JOIN                 |                             |   4202 |   841K|   841K| 5115K (0)|         |
                    |  13 |              TABLE ACCESS FULL        | BSC_RPT_REBSUM_TEMP         |   4202 |       |       |          |         |
                    |  14 |              TABLE ACCESS FULL        | BSC_RPT_REBSUM_S1           |  83564 |       |       |          |         |
                    |  15 |             MAT_VIEW ACCESS FULL      | BSC_RPT_REBSUM_MEM_MV       |  52596 |       |       |          |         |
                    |* 16 |            TABLE ACCESS BY INDEX ROWID| MN_BUCKET_LINE              |      1 |       |       |          |         |
                    |* 17 |             INDEX RANGE SCAN          | REBATEPAYMENTGRP            |   1465 |       |       |          |         |
                    |* 18 |           INDEX RANGE SCAN            | BSC_RPT_STRUCT_LI_MV_IDX1   |     22 |       |       |          |         |
                    |  19 |         MAT_VIEW ACCESS FULL          | BSC_RPT_ITEM_PH6_MV         |    641K|       |       |          |         |
                    |* 20 |        INDEX UNIQUE SCAN              | MN_10291_PK                 |      1 |       |       |          |         |
                    |  21 |       TABLE ACCESS BY INDEX ROWID     | BSC_SPLIT_PMT               |      1 |       |       |          |         |
                    |* 22 |        INDEX RANGE SCAN               | BSC_904200_IDX1             |      1 |       |       |          |         |
                    ---------------------------------------------------------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                       2 - filter("RPT"."RNK"=1)
                       4 - filter(ROW_NUMBER() OVER ( PARTITION BY "BUCK_LI"."BUCKET_LINE_ID" ORDER BY
                                  INTERNAL_FUNCTION("ITEM_VW"."BSC_PHLEVEL") DESC )<=1)
                       7 - access("LIMV"."CAT_MAP_ID"="ITEM_VW"."CAT_MAP_ID" AND "ITEM_VW"."ITEM_ID"="BUCK_LI"."SALE_ITEM_ID")
                      11 - access("MEM_MV"."PRC_PROGRAM_ID"="S1"."PRC_PROGRAM_ID" AND
                                  "MEM_MV"."PARENT_MEMBER_ID"="S1"."MEMBER_ID_CUST")
                      12 - access("TEMP"."CONTRACT_ID"="S1"."CONTRACT_ID" AND "TEMP"."REBATE_PMT_ID"="S1"."REBATE_PMT_ID")
                      16 - filter(("BUCK_LI"."PMT_BENEFIT_ID" IS NOT NULL AND "BUCK_LI"."INCLUSION_TYPE"='INC' AND
                                  "MEM_MV"."CHILD_MEMBER_ID"="BUCK_LI"."SALE_CONTRACTED_CUST_ID"))
                      17 - access("TEMP"."REBATE_PMT_ID"="BUCK_LI"."REBATE_PMT_ID")
                      18 - access("LIMV"."PRC_PROGRAM_ID"="S1"."PRC_PROGRAM_ID")
                      20 - access("PRC"."PRC_PROGRAM_ID"="S1"."PRC_PROGRAM_ID")
                      22 - access("TEMP"."REBATE_PMT_ID"="SPLIT"."REBATE_PMT_ID")
                           filter("SPLIT"."REBATE_PMT_ID" IS NOT NULL)
                    
                    Note
                    -----
                       - dynamic sampling used for this statement
                       - Warning: basic plan statistics not available. These are only collected when:
                           * hint 'gather_plan_statistics' is used for the statement or
                           * parameter 'statistics_level' is set to 'ALL', at session or system level
                            
                    
                    INSERT INTO BSC_RPT_REBSUM_S3_TEMP
                                (sale_id, struct_doc_id, pdflg, member_id_cust, paid_end_date, payee, map_name, bsc_phlevel, bsc_phcode, bsc_old_material_num, sold_to_cust_id, member_grp_name, 
                                   prc_program_id, bsc_r_af_type, rebate_pmt_id_num, rebate_pmt_id, sale_inv_qty, sales, earn_rebate_amt)
                       SELECT   rpt.sale_id, rpt.struct_doc_id, rpt.pdflg, rpt.member_id_cust, rpt.paid_end_date, rpt.payee, rpt.map_name, rpt.bsc_phlevel,
                                rpt.bsc_phcode, rpt.bsc_old_material_num, rpt.sold_to_cust_id, rpt.member_grp_name, rpt.prc_program_id, rpt.bsc_r_af_type, rpt.rebate_pmt_id_num, 
                                   rpt.rebate_pmt_id, rpt.sales_units, rpt.sales, SUM (rpt.earn_rebate_amt) earn_rebate_amt
                           FROM (SELECT DISTINCT buck_li.sale_id, temp.contract_id struct_doc_id,
                                                 temp.pdflg, s1.member_id_cust,
                                                 temp.paid_end_date, SPLIT.payee,
                                                 item_vw.map_name, item_vw.bsc_phlevel,
                                                 item_vw.bsc_phcode, item_vw.bsc_old_material_num,
                                                 buck_li.sale_contracted_cust_id sold_to_cust_id,
                                                 mem_mv.child_name member_grp_name, s1.prc_program_id, s1.bsc_r_af_type,
                                                 s1.rebate_pmt_id_num, s1.rebate_pmt_id,
                                                 buck_li.sale_inv_qty AS sales_units,
                                                 NULLIF (buck_li.sale_ext_amt, 0) sales,
                                                 NULLIF
                                                     (buck_li.bsc_payment_amount, 0) earn_rebate_amt,
                                                 ROW_NUMBER () OVER (PARTITION BY buck_li.bucket_line_id ORDER BY item_vw.bsc_phlevel DESC)
                                       rnk
                                            FROM bsc_rpt_rebsum_s1 s1,
                                                 mn_bucket_line buck_li,
                                                 bsc_rpt_item_ph6_mv item_vw,
                                                 bsc_rpt_rebsum_struct_li_mv limv,
                                                 bsc_rpt_rebsum_mem_mv mem_mv,
                                                 bsc_rpt_rebsum_temp temp,
                                                 bsc_split_pmt SPLIT,
                                                 mn_prc_program prc
                                           WHERE temp.contract_id = s1.contract_id
                                             AND temp.rebate_pmt_id = s1.rebate_pmt_id
                                             AND temp.rebate_pmt_id = SPLIT.rebate_pmt_id(+)
                                             AND temp.rebate_pmt_id = buck_li.rebate_pmt_id
                                             AND limv.prc_program_id = s1.prc_program_id
                                             AND prc.prc_program_id = s1.prc_program_id
                                             AND mem_mv.prc_program_id = s1.prc_program_id
                                             AND mem_mv.parent_member_id = s1.member_id_cust
                                             AND limv.cat_map_id = item_vw.cat_map_id
                                             AND item_vw.item_id = buck_li.sale_item_id
                                             AND mem_mv.child_member_id = buck_li.sale_contracted_cust_id
                                             AND buck_li.pmt_benefit_id IS NOT NULL
                                             AND buck_li.inclusion_type = 'INC') rpt
                          WHERE rpt.rnk = 1
                       GROUP BY rpt.sale_id,
                                rpt.struct_doc_id,
                                rpt.pdflg,
                                rpt.member_id_cust,
                                rpt.paid_end_date,
                                rpt.payee,
                                rpt.map_name,
                                rpt.bsc_phlevel,
                                rpt.bsc_phcode,
                                rpt.bsc_old_material_num,
                                rpt.sold_to_cust_id,
                                rpt.member_grp_name,
                                rpt.prc_program_id,
                                rpt.bsc_r_af_type,
                                rpt.rebate_pmt_id_num,
                                rpt.rebate_pmt_id,
                                rpt.sales_units,
                                rpt.sales;
                         
                         
                    COLUMN_NAME          NUM_DISTINCT  NUM_NULLS NUM_BUCKETS SAMPLE_SIZE HISTOGRAM
                    -------------------- ------------ ---------- ----------- ----------- ---------
                    BSC_OLD_MATERIAL_NUM        55394     583842           1       57907 NONE
                    BSC_NUM_SHEETS                 28          0           1      641691 NONE
                    BSC_PHCODE                  69997     207552           1      434160 NONE
                    BSC_PHLEVEL                     6     207552           1      434160 NONE
                    BSC_PROFIT_CENTER              10          0           1      641691 NONE
                    BSC_COST                    14966      42821           1      598874 NONE
                    MAP_NAME                    61277          0           1      641691 NONE
                    CAT_MAP_ID                  72241          0           1      641691 NONE
                    ITEM_ID                     64299          0           1      641691 NONE
                                   Still don't get it why 21 rows hasing with 641k - it looks a bad idea, maybe I should build histograms here?
                    Any idea.
                    • 7. Re: hash join
                      991593
                      Note that the TEMP Usage as 93M is actually 93GB as listed in http://www.antognini.ch/2009/05/wrong-information-about-temporary-space-usage/

                      I also verified it in v$sort_usage and hist views and it's accurate.

                      That's the issue we are chasing right now (optimize sort).
                      • 8. Re: hash join
                        Nikolay Savvinov
                        Hi,

                        do an event 10104 trace to see the internal workings of the hash join.

                        Best regards,
                        Nikolay
                        • 9. Re: hash join
                          Hemant K Chitale
                          With dynamic sampling being done, we can't be sure of the cardinality (num rows) estimates.

                          Have you executed this with gather_plan_statistics and then run select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); ?
                          That would show the estimate rows versus the actual rows and starts

                          Hemant K Chitale
                          • 10. Re: hash join
                            991593
                            I did a method_opt=>'for all columns' and it works like a charm now, the new plan is:

                            The sort usage came down from 93GB to less than 400 MBs now.

                            --------------------------------------------------------------------------------------------------------------
                            | Id  | Operation                              | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
                            --------------------------------------------------------------------------------------------------------------
                            |   0 | INSERT STATEMENT                       |                     |       |       | 11348 (100)|          |
                            |   1 |  SORT GROUP BY                         |                     |     1 |   174 | 11348   (1)| 00:02:39 |
                            |   2 |   VIEW                                 |                     |     1 |   174 | 11347   (1)| 00:02:39 |
                            |   3 |    SORT UNIQUE                         |                     |     1 |   223 | 11347   (1)| 00:02:39 |
                            |   4 |     WINDOW SORT PUSHED RANK            |                     |     1 |   223 | 11347   (1)| 00:02:39 |
                            |   5 |      NESTED LOOPS OUTER                |                     |     1 |   223 | 11345   (1)| 00:02:39 |
                            |   6 |       NESTED LOOPS                     |                     |     1 |   213 | 11344   (1)| 00:02:39 |
                            |   7 |        NESTED LOOPS                    |                     |     1 |   208 | 11344   (1)| 00:02:39 |
                            |   8 |         NESTED LOOPS                   |                     |  3835 |   741K| 11344   (1)| 00:02:39 |
                            |   9 |          NESTED LOOPS                  |                     |   384 | 59904 | 10576   (1)| 00:02:29 |
                            |  10 |           HASH JOIN                    |                     |  4924 |   533K|   723   (1)| 00:00:11 |
                            |  11 |            HASH JOIN                   |                     |  8232 |   546K|   531   (1)| 00:00:08 |
                            |  12 |             TABLE ACCESS FULL          | BSC_RPT_REBSUM_TEMP |  8232 |   168K|     7   (0)| 00:00:01 |
                            |  13 |             TABLE ACCESS FULL          | BSC_RPT_REBSUM_S1   | 87867 |  4032K|   523   (1)| 00:00:08 |
                            |  14 |            INDEX FULL SCAN             | MDK4                | 52557 |  2206K|   192   (1)| 00:00:03 |
                            |  15 |           TABLE ACCESS BY INDEX ROWID  | MN_BUCKET_LINE      |     1 |    45 |     2   (0)| 00:00:01 |
                            |  16 |            INDEX RANGE SCAN            | MDK1                |     1 |       |     1   (0)| 00:00:01 |
                            |  17 |          MAT_VIEW ACCESS BY INDEX ROWID| BSC_RPT_ITEM_PH6_MV |    10 |   420 |     2   (0)| 00:00:01 |
                            |  18 |           INDEX RANGE SCAN             | MDK5                |    10 |       |     1   (0)| 00:00:01 |
                            |  19 |         INDEX RANGE SCAN               | MDK2                |     1 |    10 |     0   (0)|          |
                            |  20 |        INDEX UNIQUE SCAN               | MN_10291_PK         |     1 |     5 |     0   (0)|          |
                            |  21 |       TABLE ACCESS BY INDEX ROWID      | BSC_SPLIT_PMT       |     1 |    10 |     1   (0)| 00:00:01 |
                            |  22 |        INDEX RANGE SCAN                | BSC_904200_IDX1     |     1 |       |     0   (0)|          |
                            --------------------------------------------------------------------------------------------------------------
                            • 11. Re: hash join
                              991593
                              creating histograms solved the issue