Skip to Main Content
  • Questions
  • For loop update that also includes other statements, how to simplify?

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, JS.

Asked: December 09, 2010 - 10:12 am UTC

Last updated: December 14, 2010 - 7:21 am UTC

Version: 10.2.0.3

Viewed 10K+ times! This question is

You Asked

Hi Tom:

In your q and a you said that if you can use a simple sql statement then don't complicate it by writing a procedural code. I have the follow for loop update, which takes very long of course and consumes a lot of resource. I'd like to simplify it by getting rid of the for loop, but since there are other statements inside the for loop besides the update statement, how do I do that?


FOR increc IN (SELECT * FROM INCURHT
ORDER BY company,claim#,cvgcode,calmon) LOOP
newfeat:=increc.company||TRIM(increc.claim#)||TRIM(increc.cvgcode);
IF newfeat <> curfeat THEN
curfeat:=newfeat;
features:=features+1;
pdbal:=0;
adjresbal:=0;
adjxsbal:=0;
wasreported:=FALSE;
waspaid:=FALSE;
wascwp:=FALSE;
wasclosed:=FALSE;
wasadjcapped:=FALSE;
END IF;
vrep := 0;
vpaid := 0;
vCWP := 0;
vclosed := 0;
vreopened := 0;
vactivity := 0;
vsuppaid := 0;
vadjcap := 0;
-- Handle voided payments (simple voiding balance logic)
IF waspaid AND increc.pdloss<0 AND increc.pdloss=-pdbal THEN
vpaid := -1;
waspaid := FALSE;
END IF;
-- Check for any relevant activity
IF (increc.pdloss <> 0) OR (increc.adjres <> 0) THEN
vactivity := 1;
END IF;
-- Update balances
pdbal := pdbal + increc.pdloss;
adjresbal := adjresbal + increc.adjres;
-- Do capping of adjuster incurred
-- if MI PIP, just take the MCCA back out
-- otherwise, do a normal cap
IF increc.state='MI' AND SUBSTR(increc.cvgcode,1,3)='PIP' THEN
vadjinc := increc.adjinc - increc.mccapd - increc.mccares;
vadjinccap := increc.netincloss;
vadjincxs := 0;
ELSE
vadjinc := increc.adjres + increc.pdloss;
vovercap := adjresbal + pdbal - vcaplevel;
IF vovercap>0 THEN
vadjinccap:=GREATEST(0,vcaplevel-adjresbal-pdbal+vadjinc);
vadjincxs:=vadjinc-vadjinccap;
ELSE
IF vadjinc<0 THEN
vadjinccap:=vadjinc+LEAST(-vadjinc,adjxsbal);
vadjincxs:=0-LEAST(-vadjinc,adjxsbal);
ELSE
vadjinccap:=vadjinc;
vadjincxs:=0;
END IF;
END IF;
adjxsbal:=adjxsbal+vadjincxs;
END IF;
-- check for capped (only count first time)
IF vovercap>0 AND NOT(wasadjcapped) THEN
vadjcap := 1;
wasadjcapped := TRUE;
END IF;
-- check for un-capped (was capped but now isn't)
IF vovercap<=0 AND wasadjcapped THEN
vadjcap:=-1;
wasadjcapped:=FALSE;
END IF;
-- Check for reported (any activity that was not ALAE)
-- IF ((pdbal > 0) or (adjresbal > 0) or (increc.alae=0)) and not(wasreported) then
-- IF ((pdbal > 0) or (adjresbal > 0)) and not(wasreported) then
IF NOT(wasreported) THEN
vrep := 1;
wasreported := TRUE;
END IF;
-- Check for first loss payment
IF wasreported AND pdbal>0 AND NOT(waspaid) THEN
vpaid := 1;
waspaid := TRUE;
END IF;
-- Check for supplemental payment
-- IF wasreported and (increc.pdloss > 0) and (wasclosed or wasCWP) THEN
-- vsuppaid := 1;
-- END IF;
-- Check for CWP re-open
IF wascwp AND (pdbal>0 OR adjresbal>0) THEN
vcwp := -1;
vreopened := 1;
wascwp := FALSE;
END IF;
-- Check for CWP
IF wasreported AND adjresbal = 0 AND pdbal = 0 AND NOT(wasCWP) AND NOT(wasclosed) THEN
vcwp := 1;
wascwp := TRUE;
END IF;
-- Check for regular re-open (of close w/ pay)
-- IF wasclosed and (adjresbal > 0) THEN
-- vclosed := -1;
-- vreopened := 1;
-- wasclosed := false;
-- END IF;
-- Check for close with pay
IF wasreported AND adjresbal=0 AND pdbal>0 AND NOT(wascwp) AND NOT(wasclosed) THEN
vclosed := 1;
wasclosed := TRUE;
END IF;
-- insert into temp values (curfeat, rep, vpaid, vcwp, vclosed);
-- commit;
UPDATE INCURHT i
SET i.reported=vrep,
i.paid=vpaid,
i.cwp=vcwp,
i.closed=vclosed,
i.activity=vactivity,
i.adjcapped=vadjcap,
i.actcapped=vadjcap,
i.adjinccap=vadjinccap,
i.adjincxs=vadjincxs,
i.adjinc=
CASE i.mi_pip
WHEN 'Y' THEN vadjinc
ELSE i.adjinc
END,
i.actinccap=vadjinccap,
i.actincxs=vadjincxs,
i.adjresitd=adjresbal,
i.actresitd=adjresbal,
i.pdlossitd=pdbal
WHERE i.company=increc.company
AND i.claim#=increc.claim#
AND i.cvgcode=increc.cvgcode
AND i.calmon=increc.calmon;
COMMIT;
END LOOP;
v_status:='C - CURSOR LOOP';


The incurht table has 27 million rows. So the for loop is really inefficient. Can you please give me some suggestions to make this a better process?

Thanks a lot!

JS

and Tom said...

I can outline the first step you would take - that is BULKING it up.

it would look like this:

declare

cursor c is select rowid rid, incurht.* from incurht order by company, claim#, cvgcode, calmon;
l_limit number := 1000;

begin

loop
    -- l_arrayN is a plsql index by table corresponding to your columnN - declare of the type of columnN
    fetch c bulk collect into L_ROWID_ARRAY, l_array1, l_array2, l_array3 ... LIMIT l_limit;

    for i in 1 .. l_rowid_array.count
    loop
        -- the current body of your loop goes here, however instead of an UPDATE, you would
        -- assign the values to more arrays (the set columns of your current update), eg:

        vrep_array(i) := vrep;
        vpaid_array(i) := vpaid;
        ... and so on
    end loop;

    forall i in 1 .. l_rowid_array.count
        update incurht set reported = v_rep_array(i), paid = v_paid_array, ....
         where rowid = l_rowid_array(i);

    exit when c%notfound;
end loop;


that'll fetch 1000 rows - process the data - and then bulk update 1000 rows back in the table (by rowid - no index access).



Rating

  (20 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, December 10, 2010 - 2:42 pm UTC

Hi Tom,

don't you think the op loop hit ora-1555 error since its updating the same table on which it has a loop?

Thanks


Tom Kyte
December 10, 2010 - 2:50 pm UTC

probably not...

I'll do this as a challenge :) Can anyone see why this code:

for x in (select * from big_table order by a,b,c,d)
loop
    process...
    update big_table set x=y, z=m where a=x.a and b=x.b and c=x.c and d=x.d;
    commit;
end loop;



probably would not be subject to an Ora-1555? There is a sound technical reason why in THIS CASE - it would probably work.


I'm not a fan of committing after each row - but in this case it probably doesn't hurt performance too much since in PLSQL - commits are asynchronous - we don't wait for lgwr.


Although - if it takes say 1/10000 of a second to commit - the commits would be adding 45 minutes of runtime! Bulking up to 1000 rows at a time will knock that down a lot - down to about 2.7 seconds

A reader, December 10, 2010 - 3:26 pm UTC

"probably would not be subject to an Ora-1555? There is a sound technical reason why in THIS CASE - it would probably work."

Is it since there is order by a,b,c,d?

Thanks


Tom Kyte
December 10, 2010 - 3:39 pm UTC

not going to say - until you tell us "why"

Small transactions!

Venzi, December 10, 2010 - 3:40 pm UTC

Because you do a commit after every row. So you have a lot of tiny transactions. Therefore the RBS will most likely not even grow on their own.
Oracle can reuse the RBS that just got freed up by the commit and therefore keeps the other undo data which is accessed by the select statement.
Tom Kyte
December 10, 2010 - 3:47 pm UTC

but that is the CAUSE of the ora-1555 - Oracle can reuse the RBS that just got freed up by the commit and therefore
keeps the other undo data which is accessed by the select statement.


that is exactly what CAUSES an ora-1555!!!!

your read components of statements receive an ora-1555 when the undo they need to provide a read consistent copy of the data is no longer there.

the query "select * from big_table order by a,b,c,d" must return the last row (of 27,000,000 rows) "as of the same time" as the first row. Suppose that process that loops 27,000,000 times takes 1/1000th of a second - that is 7.5 hours of runtime. That means - 7.5 hours after this query begins - it could require undo that is 7.5 hours old - but if it were overwritten.... bam - ora-1555.


no reading what we write

Flado, December 10, 2010 - 3:44 pm UTC

We won't read the rows we updated and commited, so no problem - in isolation. We can still ORA-1555 if other sessions are busy updating and commiting at the same time.
Tom Kyte
December 10, 2010 - 3:54 pm UTC

ora-1555 happens at the BLOCK level - not the row level. You could be reading and re-reading the same blocks over and over and over again.

for x in ( select * from t where indexed_column between ? and ? )
loop
   update t set ....;
   commit;
end loop;


is just asking for an ora-1555. In a single user database.

that, and...

Flado, December 10, 2010 - 3:53 pm UTC

of course, by the time we fetch the first 100 rows in the for loop we will have done all the consistent read gets we would need (because of the order by) :-)
Tom Kyte
December 10, 2010 - 3:55 pm UTC

keep going - tell us why that is so :)

temp segment

bfirgi, December 10, 2010 - 4:03 pm UTC

Because of the "order by" the data will be on the temp segment.

by definition of sort...

Flado, December 10, 2010 - 4:05 pm UTC

to output the first row, we must be sure that it is indeed the first (based on order by), so we need to have compared it to all other rows, so we need to have read the other rows via CR - bang! all CRs are done. The rows are safely in private memory or temp... Even if there are concurrent commits.
BTW my first hunch applies only to an implicit cursor without order by, I think...
Tom Kyte
December 10, 2010 - 4:13 pm UTC

bingo (and the last reviewer too)

that is it - but it would not be solely due to an implicit cursor - it would be for any cursor.

In general, for a query like:

select * from big_table order by a,b,c,d

the plan would be:

full scan -> sort order by


So, in order to get the FIRST row, we have to actually get the last row (into temp) and temp doesn't need consistent reads from undo since we already DID the consistent read.

If someone changed the optimizer mode to first rows - AND there was an index on a,b,c,d AND one of a,b,c,d was NOT NULL - then - that code would almost certainly blow up with an ora-1555 - because the optimizer would use the index to read the data sorted (really really SLOWLY).

And the odds are we'd have to read the same blocks over and over and over again - with long periods of time in between the reads and we'd need to apply the UNDO we ourselves generated hours ago and it might well not exist anymore.


But here - the supposition would be full scan -> sort order by

Ditch the Loop

Matt, December 10, 2010 - 4:12 pm UTC

Surely the goal would be to attempt to remove the loop completely and replace with a single SQL update statement. (as per Toms own mantra)

Or even better get rid of the update completely and do the calculations when the record is first posted - if that can't be done maybe delete/insert would be better than a big correlated updated.
Tom Kyte
December 10, 2010 - 4:49 pm UTC

I looked briefly at the logic - it would be hard since there are rolling totals/summations happening there. It would take a MERGE of a query against the table (using analytics and such) into the table itself again - a ton of work. This is a rare case where it MIGHT (stress that word) be better to make the single pass and update back by rowid IN BULK.

migrating the code to be bulky should be rather easy - and would have great benefit - it can be done fast. The merge statement would have taken a while - if it could be done (haven't looked at it that closely)

index?

Flado, December 10, 2010 - 4:13 pm UTC

thinking further about it, we could still get ORA-1555, if we don't sort - by accessing the table via a perfect index full scan. In which case we still may visit the same block many times. That takes care of the 'probably' part, I hope.
Tom Kyte
December 10, 2010 - 4:50 pm UTC

yes, that is it - if they changed the optimizer goal to first rows and we used an index full scan - that would ora-1555 almost for sure.

oops

Matt, December 10, 2010 - 4:14 pm UTC

... no correlation but still a big update...

without order by...

Flado, December 10, 2010 - 4:41 pm UTC

Yay!

now that the time is up, I can clarify what I meant by 'we don't read what we write':
if we didn't have an order by, and in the absence of any predicates that could invoke an indexed access, we'd just read every block of the table (via a full scan) just once before updating it (many times). We would be safe from ORA-1555 in isolation, but would still be vulnerable from concurrent commits to blocks near the "end" of the table.
Tom Kyte
December 10, 2010 - 4:52 pm UTC

ahh, but - you can get an ora-1555 reading a READ ONLY object - IF there are enough commits going on in the system.

So, if we just:

for x in (select * from big_table)
loop
  update big_table ...
  commit;
end;


that has a good chance of ora-1555'ing - in a single user database!

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:895410916429


more on "probably"

Flado, December 10, 2010 - 4:56 pm UTC

... which brings me to the observation that even in the 'full scan-sort order by' case, we ARE vulnerable to ORA-1555 due to concurrent commits while the full scan runs.

Phew!
Tom Kyte
December 10, 2010 - 5:02 pm UTC

yes! after we retrieve the first row - we are "safe" (I *just* wrote this on http://asktom.oracle.com/Misc/tiny-challenge.html#c4796381582115028967 now as a matter of fact :)) )

getting the first row would be risking the ora-1555, but not due to our commit in the for loop

Flado, December 10, 2010 - 5:37 pm UTC

"has a good chance of ora-1555'ing - in a single user database!"
-- yeees, given lots of delayed block cleanouts on the table, but doesn't automatic undo try hard to reduce that "good chance" somewhat?
Tom Kyte
December 10, 2010 - 6:35 pm UTC

automatic undo management *can* definitely help reduce it - IF and ONLY IF

a) the DBA had the foresight to set the undo retention realistically for the work performed on the system

AND

b) the DBA had the fortitude to allocate sufficient space to the undo tablespace to hold that much undo


I many times find neither A nor B.
I frequently see A but not B.
I rarely see A and B :)

Single SQL statement

A reader, December 11, 2010 - 3:52 pm UTC

Tom:

I have 10 processes running insert, update, delete into the same table around 500000 times. The total time it takes is around 3:30 minutes. The total number of records it inserts is around 30 million. We are trying to change it into to insert all at one time (simple insert statement with hash joins),update all at one time and delete all at the same time.

The insert all takes around 30 min,
update takes around 60 min--We have dropped some updatable indices
Delete takes around 60 min

Additional processing around 30 min. Basically we are not saving much time. The prototype we did initially showed promising results. However when the number of rows increased , I guess something is a miss. The difference between the original run and this run is NL join versus Hash joins in the later. Can you outline what we are missing?

It looks more like the 20 different process more efficiently.

Thanks,
Tom Kyte
December 12, 2010 - 8:10 am UTC

3:30 minutes is ambiguous to me. Is that 3 hours 30 minutes or 3 minutes 30 seconds? Sounds like 3 hours 30 minutes given the other information. It is a very strange way to present timings to someone.

Are you comparing a parallel operation with a non-parallel operation here? You don't say if you employed parallel query in the bulk operation. If not, why NOT? if you did, why don't you say so???????????


Any reason this cannot be done with a MERGE statement which is capable of inserting new, updating existing and removing others?? You give very little information there regarding the actual processing.


Based on the previous...

A reader, December 11, 2010 - 6:27 pm UTC

All our freelists and freelist groups are all adjusted accordingly.

Basically the essentials of batch processing are all met...

Thanks,

A reader, December 12, 2010 - 8:21 am UTC

Tom:

We are inserting into a permanent process table. This takes 30 min.
2. Updating the main transaction table based on the process table (this is merge). Takes more than 60 minutes. Takes more time if the indices are present.
3. Deleting the records from process table. That takes around 60 minutes.

Yes we did enable parallelism of 10.

Thanks,
Tom Kyte
December 13, 2010 - 2:05 am UTC

and how many cpus do you have. and how much memory. and what is your pga aggregate target. and did you do any analysis using AWR/statspack to see if the advisors had anything interesting to say.

I'm going to guess that your pga is set too low and you are doing many multi-pass pga operations that go to disk.

why is step 3 not a truncate?

there is really entirely insufficient detail here - "we insert into t1, we update t2 from t0, we delete from t0" - that is what this seems to boil down to - not much to go on.

A reader, December 12, 2010 - 8:26 am UTC

Tom:

The insert into the process table joins around 6 tables(some of them as big as 90 million).

Thanks,

Still subject to Delayed Block Cleanout?

Brian B, December 13, 2010 - 12:22 pm UTC

"There is a sound technical reason why in THIS CASE - it would probably work. "
...
"So, in order to get the FIRST row, we have to actually get the last row (into temp) and temp doesn't need consistent reads from undo since we already DID the consistent read. "

That makes a lot of sense as to why this query itself doesn't get an ORA-01555. You said "probably" in the original challenge... could this query still set up conditions for another reader to get an ORA-01555 due to delayed block cleanout?


Tom Kyte
December 14, 2010 - 3:38 am UTC

sure - if the optimizer was using first_rows(n) optimization and there existed an index on the columns we were ordering by - the optimizer might choose to read the table via the index - which means the last row would be retrieved perhaps hours after the first row - and might actually reside on the block where the first row was retrieved from - leading to a probable ora-1555.

Understandings !!

Rajeshwaran, Jeyabal, December 14, 2010 - 3:26 am UTC

for x in (select * from big_table order by a,b,c,d)
loop
    process...
    update big_table set x=y, z=m where a=x.a and b=x.b and c=x.c and d=x.d;
    commit;
end loop;


In order to get the FIRST row, we have to actually get the last row into temp. (No consistent reads, No before Image, so no ORA-1555) So we will not hit ORA-1555 here!

for x in (select * from big_table)
loop
    process...
    update big_table set x=y, z=m where a=x.a and b=x.b and c=x.c and d=x.d;
    commit;
end loop;


In this scenario, consistent reads is needed each row retrieved from big_table by reconstructing blocks from undo segment (before Image). If the requested block(before Image) is unavailable in undo segment then we will hit ORA-1555.

Is that my understanding is correct Tom?
Tom Kyte
December 14, 2010 - 7:21 am UTC

consistent reads are needed for each BLOCK retrieved from big_table and may need undo to reconstruct those blocks. If that undo isn't available - then ora-1555

Understandings !!

Rajeshwaran, Jeyabal, December 15, 2010 - 1:37 am UTC

Thanks for correcting me Tom!!

consistent reads are needed for each BLOCK retrieved from big_table

More to Explore

Multitenant

Need more information on Multitenant? Check out the Multitenant docs for the Oracle Database