Tuesday, June 23, 2015

INSERT /*+ APPEND */

This example demonstrates that an INSERT /*+ APPEND */ does not put rows into free space within a table, it adds them at the end instead. It also shows that Oracle inserts these rows using a DIRECT PATH mechanism. I tested it on Oracle 11.2. First I created an empty table:

SQL> conn /
Connected.
SQL> create table tab1
  2  as select * from dba_tables
  3  where 1 = 2
  4  /
 
Table created.
 
SQL>

Then I added the contents of DBA_TABLES 50 times. I did this using a normal INSERT statement:

SQL> begin
  2  for x in 1..50 loop
  3  insert into tab1
  4  select * from dba_tables;
  5  commit;
  6  end loop;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL>

I deleted all the rows so the table would contain nothing but free space. Then I checked how big it was:

SQL> delete tab1
  2  /
 
158150 rows deleted.
 
SQL> select bytes from user_segments
  2  where segment_name = 'TAB1'
  3  /
 
     BYTES
----------
  46137344
 
SQL>

I started a new session to zeroise my statistics then I reinserted the same data. This time I used an INSERT /*+ APPEND */ so that Oracle would add the rows to the end of the table, instead of putting them in the free space I had just created:

SQL> conn /
Connected.
SQL> begin
  2  for x in 1..50 loop
  3  insert /*+ append */ into tab1
  4  select * from dba_tables;
  5  commit;
  6  end loop;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL>

I checked the session’s wait events and they showed that a DIRECT PATH WRITE had been chosen:

SQL> select event, time_waited/100
  2  from v$session_event
  3  where sid = (select distinct sid from v$mystat)
  4  order by 1
  5  /
 
EVENT                               TIME_WAITED/100
----------------------------------- ---------------
Disk file operations I/O                        .02
SQL*Net message from client                     .01
SQL*Net message to client                         0
db file sequential read                         .67
direct path sync                                  0
direct path write                                .4
events in waitclass Other                       .11
log file sync                                     0
 
8 rows selected.
 
SQL>

Finally, I rechecked the size of the table. If the INSERT /*+ APPEND */ had put the rows in the free space, the size would have been unchanged. However, the table was twice as big, proving that the data had been added at the end of the table:

SQL> select bytes from user_segments
  2  where segment_name = 'TAB1'
  3  /
 
     BYTES
----------
100663296
 
SQL>

ORA-12838

If you do an INSERT /* APPEND */, you cannot query the table afterwards until you have done a COMMIT. If you try to do so, Oracle gives you an ORA-12838. You can see what I mean in the example below, which I tested on Oracle 11.2:

SQL> create table tab1
  2  as select * from dba_tables
  3  where 1 = 2
  4  /
 
Table created.
 
SQL> insert /*+ append */ into tab1
  2  select * from dba_tables
  3  /
 
3166 rows created.
 
SQL> select count(*) from tab1
  2  /
select count(*) from tab1
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after
modifying it in parallel
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> select count(*) from tab1
  2  /
 
  COUNT(*)
----------
      3166