query optmisation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: query optmisation

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    query optmisation

    Hi

    I have a query like

    Code:
    explain plan for
    select 
    a11.FS_RX_CD  FS_RX_CD, a11.VENDOR_NBR  DSD_VNDR_CD,
    max(a11.VENDOR_NAME)  DSD_VNDR_DSC, a11.INV_NBR  INV_NBR, a11.INV_DT  INV_DT,
    sum(a11.RETL_AMT)  WJXBFS1, sum(a11.CST_AMT) WJXBFS2
    from STKLDGR.IAR_DSD_PURCH_HD a11,  COMMON.V_FISCAL_WEEK a12,STKLDGR.IAR_STORE a13
    where a11.WEEK_NBR = a12.WEEK_NBR 
    and a11.STORE_NBR = a13.STORE_NBR 
    and  a11.WEEK_NBR = a13.WEEK_NBR
    and a13.RPT_STORE_IND = 'Y' 
    and a12.FISCAL_YR_NBR in (2005)
    and (a11.FS_RX_CD) in (select c23.FS_RX_CD
    from STKLDGR.IAR_STORE c21,
    (SELECT /*+ (iar_sku_history 12) */
       week_nbr, sku_nbr,        CASE
       WHEN cat_nbr = 37              THEN 57
       WHEN cat_nbr = 999              THEN 99
       ELSE cat_nbr
       END cat_nbr
       FROM iar_sku_history 
       UNION ALL
       SELECT week_nbr, sku_nbr, cat_nbr
       FROM enterprise_item_master a, fiscal_week b
       WHERE a.cat_nbr = 50 AND b.week_nbr >= 200344) c22,
    STKLDGR.CATEGORY_DIM c23 
    where c21.WEEK_NBR = c22.WEEK_NBR
    and   c22.CAT_NBR = c23.CAT_NBR  
    and c21.AREA_NBR in (4))
    group by a11.FS_RX_CD, a11.VENDOR_NBR, a11.INV_NBR,a11.INV_DT
    /
    And the plan looks like

    Code:
    select * from table(dbms_xplan.display)
    
    
    
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    |  Name                   | Rows  | Bytes |TempSpc| Cost  | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                         |  6254K|   572M|       |  1837K|       |    |
    |   1 |  SORT GROUP BY               |                         |  6254K|   572M|  1244M|  1837K|       |    |
    |   2 |   NESTED LOOPS               |                         |  6254K|   572M|       |  1791K|       |    |
    |*  3 |    HASH JOIN SEMI            |                         |  6254K|   542M|   596M|  1791K|       |    |
    |*  4 |     HASH JOIN                |                         |  6254K|   524M|       | 13901 |       |    |
    |*  5 |      TABLE ACCESS FULL       | FISCAL_WEEK             |    52 |   728 |       |     2 |       |    |
    |   6 |      PARTITION RANGE ALL     |                         |       |       |       |       |     1 | 25 |
    |*  7 |       HASH JOIN              |                         |    12M|   904M|       | 13793 |       |    |
    |*  8 |        TABLE ACCESS FULL     | IAR_STORE               |   416K|  4472K|       |   803 |     1 | 25 |
    |   9 |        TABLE ACCESS FULL     | IAR_DSD_PURCH_HD        |    26M|  1568M|       |  8415 |     1 | 25 |
    |  10 |     VIEW                     | VW_NSO_1                |  9606M|    26G|       |  5265 |       |    |
    |* 11 |      HASH JOIN               |                         |  9606M|   205G|       |  5265 |       |    |
    |  12 |       PARTITION RANGE ALL    |                         |       |       |       |       |     1 | 25 |
    |* 13 |        TABLE ACCESS FULL     | IAR_STORE               | 69388 |   542K|       |   803 |     1 | 25 |
    |* 14 |       HASH JOIN              |                         |    14M|   211M|       |  1199 |       |    |
    |  15 |        TABLE ACCESS FULL     | CATEGORY_DIM            |    93 |   651 |       |     5 |       |    |
    |  16 |        VIEW                  |                         |    14M|   113M|       |  1165 |       |    |
    |  17 |         UNION-ALL            |                         |       |       |       |       |       |    |
    |  18 |          PARTITION RANGE ALL |                         |       |       |       |       |     1 | 25 |
    |  19 |           TABLE ACCESS FULL  | IAR_SKU_HISTORY         |    14M|   123M|       |  1132 |     1 | 25 |
    |  20 |          MERGE JOIN CARTESIAN|                         |   412K|  3225K|       |  1988 |       |    |
    |* 21 |           TABLE ACCESS FULL  | ENTERPRISE_ITEM_MASTER  |  1741 |  5223 |       |   247 |       |    |
    |  22 |           BUFFER SORT        |                         |   237 |  1185 |       |  1741 |       |    |
    |* 23 |            INDEX RANGE SCAN  | WK_WK_PK                |   237 |  1185 |       |     1 |       |    |
    |* 24 |    INDEX UNIQUE SCAN         | MON_MON_PK              |     1 |     5 |       |       |       |    |
    -----------------------------------------------------------------------------------------------------------
    -----
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("A11"."FS_RX_CD"="VW_NSO_1"."$nso_col_1")
       4 - access("A11"."WEEK_NBR"="A"."WEEK_NBR")
       5 - filter("A"."FISCAL_YR_NBR"=2005)
       7 - access("A11"."WEEK_NBR"="A13"."WEEK_NBR" AND "A11"."STORE_NBR"="A13"."STORE_NBR")
       8 - filter("A13"."RPT_STORE_IND"='Y')
      11 - access("C21"."WEEK_NBR"="C22"."WEEK_NBR")
      13 - filter("C21"."AREA_NBR"=4)
      14 - access("C22"."CAT_NBR"="C23"."CAT_NBR")
      21 - filter("ENTERPRISE_ITEM_MASTER"."CAT_NBR"=50)
      23 - access("FISCAL_WEEK"."WEEK_NBR">=200344)
      24 - access("A"."FISCAL_MONTH_NBR"="B"."FISCAL_MONTH_NBR")
    
    Note: cpu costing is off
    
    47 rows selected.
    Any idea on how to optimize this query.Any indexes that might help

    regards
    Hrishy

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    How are you joining enterprise_item_master and fiscal_week???
    You should start by getting rid of the Cartesian product.
    this space intentionally left blank

  3. #3
    Join Date
    Oct 2002
    Posts
    182
    doesn't look like you have a join between:

    COMMON.V_FISCAL_WEEK (a12)
    and
    STKLDGR.IAR_STORE (a13)

    make sure you join all matching columns to every table.
    - Cookies

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width