Procedure Tuning--Oracle 9i!
708264Jul 1 2009 — edited Jul 9 2009I have the following procedure which is taking very very long to execute. The table which is getting updated has 50 million records. Is there any way I can completely remove cursor and implement the same funcionality in some other way. The table which is getting updated has 12-13 indexes on it. Will it make any difference if I make those indexes unusable before update and then re-build them again?
CREATE OR REPLACE PROCEDURE Art_Neg_Item_Cost_Update IS
--
v_prod_id art_neg_item_level.prod_id%TYPE;
v_busi_dte art_neg_item_level.busi_dte%TYPE;
v_ser_ltr art_neg_item_level.ser_ltr%TYPE;
v_itm_qty art_neg_item_level.itm_qty%TYPE;
v_curr_cost art_prod_series.curr_cost%TYPE;
v_ord_no art_neg_item_level.ord_no%TYPE;
--
CURSOR get_transaction_data IS
SELECT prod_id, ser_ltr, busi_dte, itm_qty, ord_no
FROM art_neg_item_level
WHERE extd_cost=0;
--
BEGIN
--
-- Loop through the import data, parse out the string to variables
OPEN get_transaction_data;
LOOP
FETCH get_transaction_data INTO v_prod_id, v_ser_ltr, v_busi_dte, v_itm_qty, v_ord_no;
EXIT WHEN get_transaction_data%NOTFOUND;
BEGIN
SELECT a.curr_cost
INTO v_curr_cost
FROM art_prod_series a
WHERE a.eff_dte = (SELECT MAX(c.eff_dte)
FROM art_prod_series c
WHERE trim(a.prod_id) = trim(c.prod_id)
AND trim(a.ser_ltr) = trim(c.ser_ltr)
AND c.eff_dte <= v_busi_dte
AND c.curr_cost <> 0
)
AND trim(v_prod_id) = trim(a.prod_id)
AND trim(v_ser_ltr) = trim(a.ser_ltr);
IF SQL%ROWCOUNT <> 0 THEN
UPDATE art_neg_item_level
SET cost = v_curr_cost,
extd_cost = v_curr_cost*v_itm_qty
WHERE TRIM(prod_id) = TRIM(v_prod_id)
AND TRIM(ser_ltr) = TRIM(v_ser_ltr)
AND busi_dte = v_busi_dte
AND itm_qty = v_itm_qty
AND TRIM(ord_no) = TRIM(v_ord_no)
AND extd_cost = 0;
IF MOD(SQL%rowcount,10000)=0 THEN COMMIT;
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
CLOSE get_transaction_data;
COMMIT;
END Art_Neg_Item_Cost_Update;
/