Hi all,
I have written the following statement.
Its basically a simple update statement with three conditions...
this is my primary table : TDMCUST
columns accessed : CUST_FA_FLAG,CUST_ST,MDB_VALID_STATE_IND
Condition 1: If Foreign Flag (CUST_FA_FLAG) = 'N' and
State (CUST_ST) in <List of States> then
update MDB_VALID_STATE_IND = 'Y'
Condition 2: If Foreign Flag (CUST_FA_FLAG) = 'Y' then
update MDB_VALID_STATE_IND = 'N'
Condition 3: If Foreign Flag (CUST_FA_FLAG) = 'N' and
State (CUST_ST) NOT IN <List of States> then
update MDB_VALID_STATE_IND = 'N'
Rownum in cursor is just to restrict number of rows.
I have 15million rows in this table, hence this query runs for hours and hours and finally says
'Could not extend rollback segment' and terminates.
CAN ANYONE TELL ME HOW EFFICIENLTY THIS QUERY CAN BE MODIFIED INORDER TO AVOID ROLLBACK SEGMENT PROBLEM AS WELL AS IMPROVE EXECUTION TIME.
Statement attached for reference.
Thanks
Prabhu
**************
DECLARE
proc_flag NUMBER := 1;
ctr NUMBER :=0;
cnt NUMBER :=0;
cursor cur is select CUST_FA_FLAG,CUST_ST,rowid from TDMCUST
where CUST_ST IS NOT NULL
and MDB_VALID_STATE_IND is NULL and rownum <1000;
BEGIN
WHILE (proc_flag = 1) LOOP
for cur_rec in cur loop
exit when cur%NOTFOUND;
begin
if cur_rec.CUST_FA_FLAG='N' and cur_rec.CUST_ST in ('AL','AK','AZ','AR','CA','CO','CT','DE','DC','FL','GA','HI','ID','IL','IN','IA','KS',
'KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND',
'OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY') then
update TDMCUST set MDB_VALID_STATE_IND='Y'
where rowid=cur_rec.rowid;
elsif cur_rec.CUST_FA_FLAG='Y' then
update TDMCUST set MDB_VALID_STATE_IND='N'
where rowid=cur_rec.rowid;
elsif cur_rec.CUST_FA_FLAG='N' and cur_rec.CUST_ST not in ('AL','AK','AZ','AR','CA','CO','CT','DE','DC','FL','GA','HI','ID','IL','IN','IA','KS',
'KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND',
'OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY') then
update TDMCUST set MDB_VALID_STAT_IND='N'
where rowid=cur_rec.rowid;E
end if;
end;
end loop;
commit;
cnt:=0;
--dbms_output.put_line('Counter value Before is =' || cnt);
select 1 into cnt from TDMCUST where CUST_ST IS NOT NULL AND MDB_VALID_STATE_IND is NULL and rownum<2;
--dbms_output.put_line('Counter value After is =' || cnt);
if (cnt=1) then
--dbms_output.put_line('Counter value is =' || cnt);
proc_flag :=1;
else
proc_flag :=0;
end if;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
|
] 
Blog
Search
Help
Members
Register
Login
Home
