Re: INSERT millions_of_rows, how to COMMIT every 10,000 rows ?

This has poor performance since PL/SQL - SQL context switching. If the destination table is empty then drop it and recreate using CREATE TABLE [new table] AS SELECT ...
If destination table isn't empty or dropping table would cause unwanted side effects (e.g. stored procedure invalidation), try this:

INSERT /*+ APPEND */ INTO [new table] SELECT ... FROM [old table]

It avoids using rollback segment. After this statement completed you have to submit COMMIT before any DML into [new table].

Regards,
Bert Received on Mon Mar 28 2005 - 02:23:05 CST