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

Comments embedded.

Bertalan.noospaam.Gombos_at_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 Received on Mon Mar 28 2005 - 15:30:42 CST