query optmisation

[hrishy]

Hi

I have a query like

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

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

[gandolf989]

How are you joining enterprise_item_master and fiscal_week???
You should start by getting rid of the Cartesian product.

[Cookies]

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.