Inserts with APPEND Hint

append does a direct path load (if it can, it is not a promise, you are requesting and we may or may not do it for you - silently)

 

if you direct path load, the transaction that did the direct path load CANNOT query that segment - but other transactions can, they just cannot see the newly loaded data.

if you direct path load, you never use any existing free space, it always writes above the high water mark.

 

if you direct path load, we bypass UNDO on the table - only the table - modifications

 

if you direct path load, you'll maintain indexes - we build mini indexes on the newly loaded data and merge them into the 'real' indexes in bulk. A direct path load of large amounts of data will maintain indexes very efficiently.

 

if you direct path load you can bypass redo on the TABLE in archivelog mode, if the database is set up to allow nologging and you have the segment set to nologging

 

direct path loading bypasses the buffer cache, you write directly to the datafiles.

 

direct path loading is only appropriate for the first load of a segment or an increment load of lots of data - or an increment load into a table that never has any deletes (so there is no free space to consider)

 

transactional systems - you probably won't use it.
warehouses - a tool you'll use a lot

 

insert /*+ append */ will ignore the append hint and use conventional path loading when the table has referential integrity or a trigger...

consider:

ops$tkyte%ORA9IR2> create table p( x int primary key);

 

Table created.

 

ops$tkyte%ORA9IR2> create table c ( x int );

 

Table created.

 

ops$tkyte%ORA9IR2>

ops$tkyte%ORA9IR2> insert into p (x) values (1);

 

1 row created.

 

ops$tkyte%ORA9IR2>

ops$tkyte%ORA9IR2> insert /*+ append */ into c select 1 from dual;

 

1 row created.

 

ops$tkyte%ORA9IR2> select * from c;

select * from c
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

 

that shows we did a direct path operation, definitely.  You cannot read from a table in the same transaction that direct path loads it... Now

 

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> commit;

 

Commit complete.

 

ops$tkyte%ORA9IR2> alter table c add constraint c_fk_p foreign key(x) references p(x);

 

Table altered.

 

ops$tkyte%ORA9IR2> insert /*+ append */ into c select 1 from dual;

 

1 row created.

 

ops$tkyte%ORA9IR2> select * from c;

         X
----------
         1
         1

 

that shows the append was ignored, referential integrity makes append "go away"

 

ops$tkyte%ORA9IR2> alter table c drop constraint c_fk_p;

 

Table altered.

 

ops$tkyte%ORA9IR2> insert /*+ append */ into c select 1 from dual;

 

1 row created.

 

ops$tkyte%ORA9IR2> select * from c;

select * from c
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

 

so we are back to being able to direct path... with append

 

ops$tkyte%ORA9IR2> create or replace trigger c before insert on c for each row
  2  begin
  3          null;
  4  end;
  5  /

 

Trigger created.

 

ops$tkyte%ORA9IR2> insert /*+ append */ into c select 1 from dual;

 

1 row created.

 

ops$tkyte%ORA9IR2> select * from c;

         X
----------
         1
         1
         1
         1

and apparently we did not direct path - we can read the table..

 

ops$tkyte%ORA9IR2> drop trigger c;

 

Trigger dropped.

 

ops$tkyte%ORA9IR2> insert /*+ append */ into c select 1 from dual;

 

1 row created.

 

ops$tkyte%ORA9IR2> select * from c;

select * from c
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

 

and now back to being able to again.

 

 

sqlldr can and does ignore triggers/referential integrity and even uniqueness during a direct path load, insert /*+ append */ does not - you are not using direct path when they (constraints) exist.

parallel is always a direct path, if you go parallel, you will be appending.