안녕하세요
뷰링크걸어서 수행되는 쿼리인데요. 속도가 느리네요. 어떻게 해야되나요?
LC_PRODUCT : 104924건
RMD.MT_PRODUCT_V2@RMD : 771408건
RMD.OE_BATCH@RMDL : 25503건
SELECT /*+DRIVING_SITE(C)*/ A.PRODUCT_CODE, A.ORDER_SEQ, A.ORDER_EA_QTY ORDER_QUANTITY,
A.BASIC_PRICE, A.BASIC_AMOUNT, A.DELIVERY_DATE,
A.SALE_PRICE, A.SALE_AMOUNT
FROM RMD.OE_BATCH@RMDL A,
LC_PRODUCT B,
RMD.MT_PRODUCT_V2@RMDL C
WHERE A.PRODUCT_CODE = B.PRODUCT_CODE
AND A.PRODUCT_CODE = C.PRODUCT_CODE
AND B.CENTER_CODE = '2920'
AND A.ORDER_DATE = '20110918'
AND A.STORE_CODE = '2701'
AND A.PO_VENDOR_CODE = '00'||'2920'
ORDER BY A.PRODUCT_CODE,DECODE(B.ICE_FREEZE_FLAG, NULL, ' ', B.ICE_FREEZE_FLAG);
Viw 생성쿼리
==========
CREATE OR REPLACE VIEW MT_PRODUCT_V2 ( PRODUCT_CODE,
ORIGINAL_CODE, PRODUCT_NAME, PRODUCT_SHORT_NAME, PRODUCT_UNIT,
PRODUCT_GAGE, GROUP_CODE, CLASS_CODE, VENDOR_CODE,
PO_VENDOR_CODE, ORDER_UNIT_FLAG, INNER_QTY, TREAT_STORE_FLAG,
ORDER_TYPE, PURCHASE_TYPE, MAKER_NAME, BASIC_PRICE,
SALE_PRICE, WH_BASIC_PRICE, STANDARD_SALE_PRICE, DC_SALE_PRICE,
BENEFIT_RATE, COMMISSION_RATE, COUPON_FLAG, TREAT_START_DATE,
ORDER_END_DATE, SALE_END_DATE, PRICE_MIN_GAGE, PRICE_MIN_UNIT,
PRICE_MIN_QTY, CONVERT_CODE, CURRENT_INV_FLAG, STRATEGY_FLAG,
DELIVERY_FLAG, FASHION_FLAG, NEW_PRODUCT_FLAG, STRATEGY_PRODUCT_FLAG,
STRATEGY_DELIVERY_DATE, PURCHASE_TAX_FLAG, SALE_TAX_FLAG, PURCHASE_TAX_RATE,
SALE_TAX_RATE, LIQUOR_KIND, EMPTY_BOTTLE_CODE, EMPTY_BOX_CODE,
EMPTY_BOTTLE_PRICE, EMPTY_BOX_PRICE, EMPTY_BOTTLE_QTY, EMPTY_BOTTLE_AMOUNT,
LIQUOR_CAPACITY, PALLET_WIDTH, PALLET_VERTICAL, PALLET_HEIGHT,
PALLET_WEIGTH, PALLET_QTY, BOX_WIDTH, BOX_VERTICAL,
BOX_HEIGHT, BOX_WEIGTH, BOX_QTY, CASE_WIDTH,
CASE_VERTICAL, CASE_HEIGHT, CASE_WEIGTH, CASE_QTY,
PRODUCT_WIDTH, PRODUCT_VERTICAL, PRODUCT_HEIGHT, PRODUCT_WEIGTH,
CREATE_DATE, CREATE_BY, LAST_UPDATE_DATE, LAST_UPDATE_BY,
DETAIL_CODE1, DETAIL_CODE2, DETAIL_CODE3, BUYER_CODE
) AS SELECT product_code AS "PRODUCT_CODE", product_code AS "ORIGINAL_CODE",
product_name, product_short_name, product_unit, product_gage,
f_group_code_conversion (class_code) group_code, class_code,
vendor_code, po_vendor_code, order_unit_flag, inner_qty,
treat_store_flag, order_type, purchase_type, maker_name,
basic_price, sale_price, wh_basic_price, standard_sale_price,
dc_sale_price, benefit_rate, commission_rate, coupon_flag,
treat_start_date, order_end_date, sale_end_date, price_min_gage,
price_min_unit, price_min_qty, convert_code, current_inv_flag,
strategy_flag, delivery_flag, fashion_flag, new_product_flag,
strategy_product_flag, strategy_delivery_date, purchase_tax_flag,
sale_tax_flag, purchase_tax_rate, sale_tax_rate, liquor_kind,
empty_bottle_code, empty_box_code, empty_bottle_price,
empty_box_price, empty_bottle_qty, empty_bottle_amount,
liquor_capacity, pallet_width, pallet_vertical, pallet_height,
pallet_weigth, pallet_qty, box_width, box_vertical, box_height,
box_weigth, box_qty, case_width, case_vertical, case_height,
case_weigth, case_qty, product_width, product_vertical,
product_height, product_weigth, create_date, create_by,
last_update_date, last_update_by, '' AS "DETAIL_CODE1",
'' AS "DETAIL_CODE2", '' AS "DETAIL_CODE3",
f_buyer_code ('2701', class_code, 1)
FROM mt_product
UNION ALL
SELECT b.product_code, b.original_code, a.product_name,
a.product_short_name, a.product_unit,
a.product_gage
|| '/'
|| f_detail_name (detail_code1)
|| '-'
|| f_detail_name (detail_code2),
f_group_code_conversion (a.class_code) group_code, a.class_code,
a.vendor_code, a.po_vendor_code, a.order_unit_flag, a.inner_qty,
a.treat_store_flag, a.order_type, a.purchase_type, a.maker_name,
a.basic_price, a.sale_price, a.wh_basic_price,
a.standard_sale_price, a.dc_sale_price, a.benefit_rate,
a.commission_rate, a.coupon_flag, a.treat_start_date,
b.order_end_date, b.sale_end_date, a.price_min_gage,
a.price_min_unit, a.price_min_qty, a.convert_code,
a.current_inv_flag, a.strategy_flag, a.delivery_flag,
a.fashion_flag, a.new_product_flag, a.strategy_product_flag,
a.strategy_delivery_date, a.purchase_tax_flag, a.sale_tax_flag,
a.purchase_tax_rate, a.sale_tax_rate, a.liquor_kind,
a.empty_bottle_code, a.empty_box_code, a.empty_bottle_price,
a.empty_box_price, a.empty_bottle_qty, a.empty_bottle_amount,
a.liquor_capacity, a.pallet_width, a.pallet_vertical,
a.pallet_height, a.pallet_weigth, a.pallet_qty, a.box_width,
a.box_vertical, a.box_height, a.box_weigth, a.box_qty, a.case_width,
a.case_vertical, a.case_height, a.case_weigth, a.case_qty,
a.product_width, a.product_vertical, a.product_height,
a.product_weigth, a.create_date, a.create_by, a.last_update_date,
a.last_update_by, b.detail_code1, b.detail_code2, b.detail_code3,
f_buyer_code ('2701', a.class_code, 1)
FROM mt_product a, mt_product_style b
WHERE a.product_code = b.original_code |