Home » SQL & PL/SQL » SQL & PL/SQL » Update - Rollback Problem
| Update - Rollback Problem [message #41195] |
Mon, 09 December 2002 15:06  |
Prabhu
Messages: 26
Registered: October 2001
|
Junior Member |
|
|
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 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 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;
|
|
|
|
| Re: Update - Rollback Problem [message #41197 is a reply to message #41195] |
Mon, 09 December 2002 15:21  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
1) Size your rollback appropriately for the types of transactions you do. Rollback is not a scarce resource - don't try to keep it as small as possible. Make it as big as you need. Your segments are too small.
2) Do the update in one, non-procedural statement. No loops, no periodic commits, no rownum filters - just an update statement and a single commit:
update tdmcust
set mdb_valid_state_ind = case
when cust_fa_flag = 'Y' then 'N'
when cust_st not in ('AL', ..., 'WY') then 'N'
else 'Y'
end; |
|
|
|
| Re: Update - Rollback Problem [message #41214 is a reply to message #41197] |
Tue, 10 December 2002 06:31 |
Rick Cale
Messages: 111
Registered: February 2002
|
Senior Member |
|
|
What version of Oracle are you using? There are more
options in 9i for managing rollback segments such as
undo management. Also resumable space management may
also be of help.
|
|
|
|
|
|
Goto Forum:
Current Time: Tue May 14 23:39:48 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
