A direct-path insert writes new rows into fresh blocks above the table's high-water mark and sends them to the data files directly, bypassing the buffer cache. It is requested with the APPEND hint on an INSERT ... SELECT, and it is the method behind most bulk loads. It skips the undo for the inserted rows, can skip most redo, and gathers statistics as it loads, but it also locks the table and leaves free space below the high-water mark unused. This guide compares a conventional and a direct-path insert of 500,000 rows and shows when the direct path really pays off.
Syntax
insert /*+ append */ into target_table select ... from source_table;
APPEND applies to INSERT ... SELECT. For INSERT ... VALUES, typically with FORALL, the hint is APPEND_VALUES.
Sample Tables
A source table with 500,000 orders and two empty tables with the same structure:
Example:
create table demo_orders_src as
select level as order_id, mod(level * 7, 5000) + 1 as customer_id,
date '2025-01-01' + mod(level, 600) as order_date,
mod(level * 13, 500) + 10 as amount, rpad('x', 50, 'x') as note
from dual connect by level <= 500000;
create table demo_orders_conv for exchange with table demo_orders_src;
create table demo_orders_direct for exchange with table demo_orders_src;
select log_mode, force_logging from v$database;Output:
Table DEMO_ORDERS_SRC created. Table DEMO_ORDERS_CONV created. Table DEMO_ORDERS_DIRECT created. LOG_MODE FORCE_LOGGING _____________ ________________ ARCHIVELOG YES
The database runs in ARCHIVELOG mode with FORCE LOGGING, as production databases with a standby usually do. That matters for the redo figures below.
The Plans
Example:
explain plan for insert into demo_orders_conv select * from demo_orders_src; select * from dbms_xplan.display(format => 'BASIC +ROWS'); explain plan for insert /*+ append */ into demo_orders_direct select * from demo_orders_src; select * from dbms_xplan.display(format => 'BASIC +ROWS');
Output:
Explained. PLAN_TABLE_OUTPUT ________________________________________________________________ Plan hash value: 1746386434 ------------------------------------------------------------- | Id | Operation | Name | Rows | ------------------------------------------------------------- | 0 | INSERT STATEMENT | | 500K| | 1 | LOAD TABLE CONVENTIONAL | DEMO_ORDERS_CONV | | | 2 | TABLE ACCESS FULL | DEMO_ORDERS_SRC | 500K| ------------------------------------------------------------- 9 rows selected. Explained. PLAN_TABLE_OUTPUT __________________________________________________________________________ Plan hash value: 2506501341 ----------------------------------------------------------------------- | Id | Operation | Name | Rows | ----------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 500K| | 1 | LOAD AS SELECT | DEMO_ORDERS_DIRECT | | | 2 | OPTIMIZER STATISTICS GATHERING | | 500K| | 3 | TABLE ACCESS FULL | DEMO_ORDERS_SRC | 500K| ----------------------------------------------------------------------- 10 rows selected.
- LOAD TABLE CONVENTIONAL is the normal insert: rows go into blocks in the buffer cache, wherever there is free space.
- LOAD AS SELECT is the direct-path insert. OPTIMIZER STATISTICS GATHERING collects table statistics while the rows pass, because the target is empty.
Compare Time, Redo, and Undo
Both inserts run in one session, which reads its own redo and undo statistics from V$MYSTAT before and after each:
Example:
set serveroutput on
declare
l_start number;
l_redo0 number;
l_undo0 number;
function stat (p_name varchar2) return number is
l_value number;
begin
select s.value into l_value
from v$mystat s join v$statname n on n.statistic# = s.statistic#
where n.name = p_name;
return l_value;
end;
procedure report (p_label varchar2) is
begin
dbms_output.put_line(rpad(p_label, 14)
|| lpad((dbms_utility.get_time - l_start) * 10, 5) || ' ms, '
|| round((stat('redo size') - l_redo0) / 1024 / 1024, 1) || ' MB redo, '
|| round((stat('undo change vector size') - l_undo0) / 1024 / 1024, 1) || ' MB undo');
end;
begin
l_redo0 := stat('redo size'); l_undo0 := stat('undo change vector size');
l_start := dbms_utility.get_time;
insert into demo_orders_conv select * from demo_orders_src;
commit;
report('conventional:');
l_redo0 := stat('redo size'); l_undo0 := stat('undo change vector size');
l_start := dbms_utility.get_time;
insert /*+ append */ into demo_orders_direct select * from demo_orders_src;
commit;
report('direct-path:');
end;
/Output:
conventional: 100 ms, 42.8 MB redo, 1.6 MB undo direct-path: 280 ms, 41.9 MB redo, 0 MB undo PL/SQL procedure successfully completed.
- Undo: the conventional insert generated 1.6 MB of undo, the direct-path insert none. Rolling back a direct-path insert just discards the blocks above the old high-water mark.
- Redo: about 42 MB both times. With FORCE LOGGING, every change is logged anyway. Without FORCE LOGGING, a direct-path insert into a NOLOGGING table skips the redo for the data, which is where large loads gain most; the table then needs a new backup, because the loaded data cannot be recovered from the redo.
- Time: the conventional insert finished first here. Its rows only reached the buffer cache, and the database writer writes them to disk later, outside the measured time, while the direct-path insert wrote its blocks during the statement and also gathered statistics. On a small, fast system like this one the difference is not in the direct path's favor; with tens of millions of rows, a full buffer cache, NOLOGGING, or parallel DML, it is.
Statistics Gathered Online
Example:
select table_name, num_rows, to_char(last_analyzed, 'HH24:MI:SS') as analyzed
from user_tab_statistics
where table_name in ('DEMO_ORDERS_CONV', 'DEMO_ORDERS_DIRECT')
order by table_name;Output:
TABLE_NAME NUM_ROWS ANALYZED _____________________ ___________ ___________ DEMO_ORDERS_CONV DEMO_ORDERS_DIRECT 500000 04:24:06
The direct-path insert into the empty table left statistics for 500,000 rows. The conventional insert left none, so that table needs a DBMS_STATS gathering before queries can be planned well.
Reading the Table in the Same Transaction
Example:
insert /*+ append */ into demo_orders_direct select * from demo_orders_src where order_id <= 10; select count(*) from demo_orders_direct; rollback;
Output:
10 rows inserted.
COUNT(*)
___________
500010
Rollback complete.In Oracle AI Database 26ai, the session can query the table right after a direct-path insert, before committing. Earlier releases raise ORA-12838 for this, and code written for them commits immediately after each direct-path insert.
Things to Know
- A direct-path insert locks the whole table until commit; other sessions cannot insert, update, or delete in it meanwhile. Use it for batch loads, not for concurrent OLTP inserts.
- It does not reuse free space below the high-water mark, so tables loaded this way after deletes keep growing.
- Oracle silently falls back to a conventional insert in some cases, for example when the table has enabled foreign keys or triggers; check the plan for LOAD AS SELECT.
- Indexes are maintained at the end of the load, in bulk, which is more efficient than row by row.
Related Guides
Conclusion
INSERT /*+ APPEND */ loads rows above the high-water mark, bypassing the buffer cache, without undo for the data, and with statistics gathered on the way. Its biggest gain, avoiding redo, needs a NOLOGGING table in a database without FORCE LOGGING; under FORCE LOGGING the gains are the missing undo and the bulk index maintenance. Measure on your own system, and keep the table lock in mind.
