HI,
could u someone give the idea to improve performace for below query.
WHERE A.BAT_PROMO_NBR = B.BAT_PROMO_NBR
AND A.BAT_PROMO_NBR = C.BAT_PROMO_NBR
AND A.BAT_SRC_VNDR_NO = :WS-TAG-ITEM-VENDOR
AND A.BAT_SRC_ITEM_NO = :WS-TAG-ITEM-CODE
AND A.BAT_UPC_CODE = :WS-TAG-REDEF-12
AND A.BAT_STORE IN (:WS-TAG-STORE-NUM,0)
AND A.BAT_ZONE = :WS-TAG-STORE-NUM
AND A.BAT_SRC_CODE = 2
AND ((A.BAT_PULL_IND = 'Y')
OR (A.BAT_EFF_DATE = :WS-SELL-START-DATE
AND A.BAT_PULL_IND IN ('R','S'))
AND A.BAT_CNFLT_FLAG = ' ')
AND A.BAT_CNFLT_PROMO = 0
AND B.BAT_PROMO_CLASS <> 'T'
AND C.BAT_CLU_LEVEL =
(SELECT MAX(BAT_CLU_LEVEL) FROM BATCH_TIERDTL D
WHERE D.BAT_PROMO_NBR = B.BAT_PROMO_NBR)
WITH UR
END-EXEC. we are fetching the data from 3 tables , its around 10lacs records were fething; its tooks almost 20 to 30 mints.
so someone should provide the better way to reduce cpu consumtion///
thanks,
ram