A normal INSERT looks for free space in existing blocks and writes undo for every row, which is right for transactions but slow for bulk loads. A direct-path INSERT, requested with the APPEND hint, writes formatted blocks straight above the table's high-water mark, bypassing the buffer cache and generating almost no undo for the table data. For loading or archiving many rows, it is much faster.
Code for This Guide
The main examples are in the examples/dml folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
insert /*+ append */ into table [(columns)] subquery; insert /*+ append_values */ into table values (...); -- in bulk PL/SQL
APPEND applies to INSERT ... SELECT. APPEND_VALUES requests direct path for INSERT ... VALUES, useful with FORALL in PL/SQL.
Archive Rows with a Direct-Path Load
The cancelled flights are copied into an archive table, created empty beforehand from FLIGHTS with WHERE 1 = 0.
Example:
insert /*+ append */ into flight_archive select * from flights where status = 'CANCELLED'; select count(*) as archived from flight_archive; commit;
Output:
50 rows inserted.
ARCHIVED
___________
50
Commit complete.Fifty rows are loaded and committed; the example drops the archive table afterward.
Check That It Is a Direct-Path Load
The execution plan shows LOAD AS SELECT for a direct-path insert, where a conventional insert shows LOAD TABLE CONVENTIONAL.
Example:
-- the plan shows LOAD AS SELECT: rows are written above the high-water mark explain plan for insert /*+ append */ into flight_archive2 select * from flights where status = 'CANCELLED'; select * from table(dbms_xplan.display(format => 'BASIC'));
Output:
Explained. PLAN_TABLE_OUTPUT _______________________________________________________________ Plan hash value: 509712008 ------------------------------------------------------------ | Id | Operation | Name | ------------------------------------------------------------ | 0 | INSERT STATEMENT | | | 1 | LOAD AS SELECT | FLIGHT_ARCHIVE2 | | 2 | OPTIMIZER STATISTICS GATHERING | | | 3 | TABLE ACCESS FULL | FLIGHTS | ------------------------------------------------------------ 10 rows selected.
The hint can be ignored silently, for example when the table has enabled triggers or foreign keys, so checking the plan is worthwhile for important loads.
Things to Know
- A direct-path insert locks the whole table until commit; other sessions cannot insert into it meanwhile.
- Space below the high-water mark is not reused, so frequent small direct-path inserts waste space; use it for large loads.
- Combined with NOLOGGING on the table, it can skip most redo too, but then the loaded data needs a new backup.
Related Guides
Conclusion
INSERT /*+ APPEND */ loads rows in direct path, above the high-water mark and with minimal undo, which makes large loads and archiving much faster. Confirm it with LOAD AS SELECT in the plan, and remember that it locks the table until the transaction ends.
