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