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

From: HansF <News.Hans_at_telus.net>
Date: Sun, 27 Mar 2005 14:14:30 GMT
Message-Id: <pan.2005.03.27.15.16.03.208716@telus.net>

On Sun, 27 Mar 2005 05:43:53 -0800, xtanto wrote:

> Hi All,
>
> I want to insert millions of rows from old table into new table.
> (different in structure, so that I have manipulate in the query)
>
> INSERT INTO [new table] SELECT ... FROM [old table]

Just thinking out loud ... You might also want to look at the 'CREATE TABLE AS SELECT ..." capability of Oracle.

>
> How can I do COMMIT for every 10,000 rows ?

One common reason for this request is because of a ROLLBACK SEGMENT (or UNDO) problem. If true for you, you are strongly encouraged to resize your ROLLBACK SEGMENTS instead of using a COMMIT. The interim COMMIT ultimately ends up requiring a lot more resources, can have secondary problems (such as different ROLLBACK SEGMENT issues), and is generally a *lot* slower.

If you really want to do the commit, a solution is to create a PL/SQL procedure with a loop.

/Hans Received on Sun Mar 27 2005 - 08:14:30 CST