Hi,
below is table structure:
CREATE TABLE TEST_TEMP (SEQ NUMBER, TEST_DATA VARCHAR2(1000), UPDT_TEST_DATA VARCHAR2(1000));
table having more than 3 crores of data, i want update a column with some logic.. I have written a block which is updating correctly but because of huge data its performance is very slow.
How can I use bulk collect/ forall loop or any hint like parallel which will reduce its execution time?
below is the block:
DECLARE
L_VAR1 VARCHAR2(2);
L_VAR1LENGTH VARCHAR2(4);
L_ILENGTH NUMBER;
L_COUNTER1 VARCHAR2(4) :=0;
L_VAR2 VARCHAR2(2) :='55';
LN_CNT NUMBER :=0;
CURSOR C1 IS SELECT ROWID AS ROW_ID, TEST_DATA AS TEST_DATA
FROM TEST_TEMP WHERE TEST_DATA LIKE '%5544%';
BEGIN
FOR I IN C1
LOOP
L_ILENGTH := LENGTH(I.TEST_DATA);
L_COUNTER1:=7;
BEGIN
WHILE (L_ILENGTH > L_COUNTER1)
LOOP
L_VAR1 :=(SUBSTR(I.TEST_DATA,L_COUNTER1,7))
L_COUNTER1 := L_COUNTER1 + 7;
L_VAR1LENGTH := SUBSTR(I.TEST_DATA, L_COUNTER1,2);
L_COUNTER1 := L_COUNTER1 + 7;
IF (L_VAR1 = L_VAR2)
THEN
L_COUNTER1 := L_COUNTER1 + L_VAR1LENGTH;
LN_CNT := LN_CNT +1;
UPDATE TEST_TEMP
SET UPDT_TEST_DATA = SUBSTR(I.TEST_DATA,1,L_COUNTER1-15)||SUBSTR(I.TEST_DATA,L_COUNTER1,L_ILENGTH)
WHERE ROWID = I.ROW_ID;
ELSE
L_COUNTER1:=L_COUNTER1 + L_VAR1LENGTH;
END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
END LOOP;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
/
in above block how to use bulk collect or any hint to reduce the execution time.
Thanks..