It does a direct path load to disk - bypassing the buffer cache. It does generate minimal logging for dictionary changes but not for DML insert operation intended to. So for bulk load operations it is faster but may be slow for small insertions.
A direct path load always loads above the high water mark, since it is formatting and writing blocks directly to disk - it cannot reuse any existing space.
We cannot query a table or perform any DML after direct pathing into it until you commit.
drop table t_append;
create table t_append as select * from user_objects;
create table t_append_test as select * from user_objects where 1=0;
insert /*+ APPEND */ into t_append_test
select * from t_append;
select * from t_append_test;
[Error] Execution (15: 1): ORA-12838: cannot read/modify an object after modifying it in parallel
As there are no UNDO logs generated for direct-path load DML operation, other sessions do not read data from UNDO but read it from data files below higher water mark. Oracle will not allow uncommitted changes to be read by other users above old high water mark. This is the reason that to make it consistent oracle does not allow same session to query table if changes are not committed.
As we cannot perform any DML on table, this is serial operation and no other session can perform any operation on such table.
From oracle documentation:
- During direct-path INSERT operations, Oracle appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. These procedures combined can enhance performance.
When you are inserting in parallel DML mode, direct-path INSERT is the default. In order to run in parallel DML mode, the following requirements must be met:
To disable direct-path INSERT, specify the NOAPPEND hint in each INSERT statement. Doing so overrides parallel DML mode.
As all parallel DML operations are by default direct-path insert, any subsequent DML/select statement on table from same session will fail as with the error mentioned above.
Note: If you do not care about logging for recovery, you may even disable logging by NOLOGGING hint along with append hint and it will ignore generating REDO for dml statement and may further improve performance of insertion.
From Tom:
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
Note: insert /*+ append */ will ignore the append hint and use conventional path loading when the table has referential integrity or a trigger.
drop table t_append;
create table t_append as select * from user_objects;
create table t_append_test as select * from user_objects where 1=0
insert /*+ APPEND */ into t_append_test
select * from t_append
select * from t_append_test
this statement is failing as error mentioned above.
Now define FK on child table and then load data by using append hint.
alter table t_append add constraint t_append_pk primary key(object_name,OBJECT_TYPE);
alter table t_append_test add constraint t_append_test_fk foreign key(object_name,OBJECT_TYPE) references t_append(object_name,OBJECT_TYPE);
insert /*+ APPEND */ into t_append_test
select * from t_append
select * from t_append_test;
It goes well. It means it ignored direct-path insert if table has FK defined.
Same with triggers. If table has triggers defined then oracle will ignore direct path insert and will go with conventional path.
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.
