Hi Folks,
tab_1 has 60K records
tab_2 has 90 records
Both tables are indexed
The code to update one of tab_1 columns is as follows
DECLARE
v_flag CHAR(1);
CURSOR c_provider IS
select p.PROV, p.FREE, p.SUB, p.PAID
from tab_1 p,
tab_2 vab
where p.PROV = vab.PROV
and vab.PAID is NULL;
BEGIN
FOR r_provider IN c_provider
LOOP
IF r_provider.FREE = 'Y' THEN
v_flag := 'N';
ELSIF r_provider.SUB = 'Y' THEN
v_flag := 'N';
ELSIF r_provider.PAID = 'Y' THEN
v_flag := 'Y';
END IF;
UPDATE tab_1
set PAID_FLAG = v_flag
where PROV = r_provider.PROV;
END LOOP;
commit;
END;
/
It is taking hour and a half to update a column (from NULL value to "Y" or "N") ?
Can anybody please suggest something to make the update go faster? or point out anything that might be wrong with my code?
Thanks in advance
rogers42
tab_1 has 60K records
tab_2 has 90 records
Both tables are indexed
The code to update one of tab_1 columns is as follows
DECLARE
v_flag CHAR(1);
CURSOR c_provider IS
select p.PROV, p.FREE, p.SUB, p.PAID
from tab_1 p,
tab_2 vab
where p.PROV = vab.PROV
and vab.PAID is NULL;
BEGIN
FOR r_provider IN c_provider
LOOP
IF r_provider.FREE = 'Y' THEN
v_flag := 'N';
ELSIF r_provider.SUB = 'Y' THEN
v_flag := 'N';
ELSIF r_provider.PAID = 'Y' THEN
v_flag := 'Y';
END IF;
UPDATE tab_1
set PAID_FLAG = v_flag
where PROV = r_provider.PROV;
END LOOP;
commit;
END;
/
It is taking hour and a half to update a column (from NULL value to "Y" or "N") ?
Can anybody please suggest something to make the update go faster? or point out anything that might be wrong with my code?
Thanks in advance
rogers42