Oracle Database Discussions

Hi,

I am having 15 million records in my table and i want to update it.

The current sql statement which is doing it is:

=========================================================

UPDATE DstTable tbl2 SET dstCol = (SELECT srcCol from SrcTable tbl1 where tbl1.PrimId = tbl2.PrimId and tbl1.oneMoreId = tbl2.oneMoreId )

=========================================================

The above statement works but its not the best solution from performance point of view.

I want to update 10000 records at a time i.e. do the update in batch. For this I have written loop like this:

=========================================================
Loop
UPDATE DstTable tbl2 SET dstCol = (SELECT colVal from SrcTable tbl1 where tbl1.primId = tbl2.primId and tbl1.oneMoreId = tbl2.oneMoreId )
where ROWNUM <= 10000;

EXIT WHEN SQL%ROWCOUNT < 10000;
COMMIT;

End loop;

Commit;


=========================================================

But this will only update the same first 10000 rows. How should i take care that all the records be processed.

Any help to solve this will be highly appreciated.

Thanks

PS: I dont want to use cursor which will do like
Update rec1
Update rec2 and so on till 10000 and then commmit. I don't want to do this.