|Procedure Cost Problem [message #228753]
||Wed, 04 April 2007 01:33
Registered: April 2007
In my application I wrote a procedure and access it from my VB application. Everything properly indexed but the cost is very high. Here I am sending my procedure....
CURSOR C1 IS
SELECT CASH_STATE.* FROM CASH_STATE WHERE NVL(STATUS,'A') != 'T' ;
DELETE FROM TRANSFER_LOG;
FOR RECSET IN C1 LOOP
INSERT INTO CASH (35 files are there) VALUES --
RECSET.that 35 fields of CASH table);
IF V_REGNNO IS NOT NULL AND RECEIPTNO IS NOT NULL THEN
UPDATE RC_RECEIPT SET TRANSFLAG='T' WHERE
REGNNO=V_REGNNO AND RCPTNO=RECEIPTNO;
UPDATE CASH_STATE SET STATUS='T' WHERE CHASISNO =RECSET.CHASISNO;
WHEN NO_DATA_FOUND THEN
INSERT INTO TRANSFER_LOG VALUES (RECSET.VEHREGNO,'NO RECEIPT');
two tables are there CASH_STATE and CASH. I just want to add data from CASH_STATE table to CASH table which has NULL value in STATUS field. But the problem is if there are too many that type of data then the cost is coming 2980 and the process is running very slow.
Plzzz help me..
[Updated on: Wed, 04 April 2007 06:16]
Report message to a moderator