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>