This discussion is archived
11 Replies Latest reply: Feb 21, 2013 10:31 AM by 991593 RSS

hash join

991593 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    creating histograms solved the issue

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points