If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

I have a 5 Million row table that I'm trying to recreate as a partitioned table. Basically I've created a matching, partitioned table and am using a cursor loop to read a row from the original table and inserting it into the new table. It is doing a commit after each insert which is slowing things down. Is there any way inside of a simple cursor loop to tell to only commit every X number of rows?

I'm adding this just for the sake of "elegant and efficient coding" (and because you've gotten two different PL/SQL block examples and in both authors have forgotten to reset the counter variables, which proofs such coding is not the best practice...):

You dont need a special variable to count records fetched by a cursor. PL/SQL corsor has an inbuild attribute that tells you this count: %ROWCOUNT. With it and a simple use of MOD() function you could commit every 1000 fetched records with the following:

.....
FOR r1 IN c1 LOOP
.....
IF MOD(c1%ROWCOUNT,1000) = 0 THEN COMMIT;
END IF;
END LOOP;

No need to declare a special varible for the counter, no need to increment that variable, no need to reset it after each commit. The code is more elegant and more efficient.

HTH,

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?