f



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

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]

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

Thank you for your help,
Krist

0
xtanto (76)
3/27/2005 1:43:53 PM
comp.databases.oracle.server 22978 articles. 1 followers. Post Follow

10 Replies
16508 Views

Similar Articles

[PageSpeed] 57

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
-1
News.Hans (974)
3/27/2005 2:14:30 PM
xtanto@hotmail.com wrote:

> How can I do COMMIT for every 10,000 rows ?
> 
> Thank you for your help,
> Krist

Why would you want to? In a well designed database there should
be no need to even ask the question.

So rather than asking for a kludge ... have the DBA fix the
root cause of the ORA-01555's.
-- 
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
0
DA
3/27/2005 6:48:10 PM
> 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]
>
> How can I do COMMIT for every 10,000 rows ?
>
> Thank you for your help,
> Krist
>

Hi Krist

You could use a PL/SQL block like the following -


declare
  cursor oldtab_csr is select * from oldtab;
  rec_count number := 1;
begin
  for oldtab_rec in oldtab_csr loop
    -----
    begin
      insert into newtab values (oldtab_rec.col1,oldtab_rec.col2,...);
    exception
    when others then
      dbms_output.put_line('Unable to insert record '||oldtab_rec.pkcol||' - 
error encountered: '||sqlerrm);
    end;
    -----
    rec_count := rec_count + 1;
    if mod(rec_count,10000) = 0 then
      commit;
    end if;
  end loop;
end;


If you are able to add a column to your source table, you could have a flag 
to indicate that the
record was successfully transferred, which you set with an update statement 
immediately after
the insert - then in your source cursor exclude records that have already 
been transferred,
so that you are able to restart the script at any point.


If you are merging the data where many records already exist in the 
destination table, change the insert statement above as follows:

-----
  update newtab
     set col1 = oldtab_rec.col1
       , col2 = oldtab_rec.col2
   where newtab.pkcol = oldtab_rec.pk_col;

  if sql%rowcount = 0 then
    insert into newtab values (oldtab_rec.col1,oldtab_rec.col2,...);
  end if;
-----


Alternatively if you don't have many records in newtab, then it may be more 
performant to strucure it with the insert first as follows:

-----
  insert into newtab
  select oldtab_rec.col1,oldtab_rec.col2,...
    from dual
   where not exists
         (select NULL
            from newtab
           where newtab.pkcol = oldtab_rec.pk_col);

  if sql%rowcount = 0 then
    update newtab
       set col1 = oldtab_rec.col1
         , col2=oldtab_rec.col2
     where newtab.pkcol = oldtab_rec.pk_col;
  end if;
-----

Good luck!

Mike Cretan, Senior Oracle Developer


0
3/28/2005 2:25:03 AM
If you are doing from sql plus, try using this command at the sql
prompt:
set autocommit <number>
In your case the <number> could be 10000.
Regards
RBS
HansF wrote:
> 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

0
rbs100 (5)
3/28/2005 4:12:46 AM
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

1
3/28/2005 8:23:05 AM
Comments embedded.

Bertalan.noospaam.Gombos@noospaam.g-m-a-i-l.com wrote:
> 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.

Not true, at least for 9.2.0.6.  As an example, Session #1:

SQL>  insert into rbs_test
  2>  select /*+ append */ *
  3>  from all_objects;

3311 rows created.

Session #2, monitoring the insert from Session #1:

SQL>  select r.name rr,
  2>         nvl(s.username, 'no transaction') us,
  3>         s.osuser  os,
  4>         s.terminal te
  5>  from v$lock l, v$session s, v$rollname r
  6>  where l.sid = s.sid(+)
  7>  and trunc(l.id1/65536) = r.usn
  8>  and l.type = 'TX'
  9>  and l.lmode = 6
 10>  order by r.name;

RB Segment         Username        OS User    Terminal
------------------ --------------- ---------- ----------
_SYSSMU8$          SCOTT           davidf     DFMQ5T21

Rollback segments ARE being used with the /*+ append */ hint.  If I
perform another insert, with the same statement, I find a new segment
is used:

SQL> /

RB Segment         Username        OS User    Terminal
------------------ --------------- ---------- ----------
_SYSSMU9$          SCOTT           davidf     DFMQ5T21

I'm wondering where you found this 'information', as it does not appear
to be at all valid.

> After this statement completed you
> have to submit COMMIT before any DML into [new table].
> 
> Regards,
> Bert


David Fitzjarrell

0
fitzjarrell (2006)
3/28/2005 9:30:42 PM
David,

1. You are using the wrong syntax.
     Note that the post you are referring to has append hint just after
insert.
     You however are using it after select.
      its insert /*+ append */ into newtable select * from old table;

      not

       insert into newtable select /*+ append */ from oldtable;

2. The rollback used is *minimal/near zero* .. its not zero as being
claimed.

Anurag

0
avoracle (241)
3/28/2005 10:03:04 PM
Performance issues aside (and I agree that a single SQL statement with or 
without hints would be best in this regard), it appears that this users' 
principal concern is porting data from one database to another.

The PL/SQL solution offers more flexibility with exception handling & job 
restarting than a single SQL statement, and by the time they have worked 
through all the rollback too small/snapshot too old issues, they'd have been 
better off with the slower & more predictable outcomes that PL/SQL provides.

Cheers,

Mike Cretan 


0
3/28/2005 10:30:27 PM
set autocommit <n>

in sqlplus will issue a commit after n insert, delete, update or pl/sql
block executions.

You seem to be indicating that oracle will commit after n rows
inserted.
Not so!

Anurag

1
avoracle (241)
3/28/2005 10:41:45 PM
Well,
One option could be you can generate an insert script from the old
table for every row and set the autocommit to 10000.
Another easier option could be, if you are using the toad, you generate
the insert statement for the old table  and change the name of the name
with find and replace option.  And toad itself creates commit statement
for number of records which you specify while generating the script.
Else you have write only a PL/SQL .
Hope this helps...
Regards
RBs
xtanto@hotmail.com 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]
>
> How can I do COMMIT for every 10,000 rows ?
> 
> Thank you for your help,
> Krist

0
rbs100 (5)
3/29/2005 11:17:13 AM
Reply: