APPEND, APPEND_VALUES and NOAPPEND Hints

Introduction

In Oracle SQL, the APPEND, APPEND_VALUES, and NOAPPEND hints control how data is inserted. APPEND tells Oracle to perform a direct-path insert, bypassing the buffer cache and adding rows directly to the end of the table. APPEND_VALUES applies this the INSERT ... VALUES statements. In contrast, NOAPPEND forces a conventional insert. These hints are especially helpful for improving performance in bulk loads and data warehousing tasks. 

Before we look at some examples, lets just review the difference between a standard insert and a direct path insert

 Conventional Insert Versus a Direct Path Insert 

Conventional Insert

In a conventional insert, the inserts go through the buffer cache. Here are the high-level steps:

1. An UNDO segment is assigned to manage undo data.

2. Oracle locates the relevant table blocks (based on row location or free space).

3. If they’re not already in memory, they’re read from disk into the buffer cache.

4. The new row(s) are inserted into the in-memory blocks inside the buffer cache.

5. Oracle looks for space inside existing blocks, not just appending at the end like in direct-path.

6. Generate Undo and Redo

• Undo is created to support rollback and read consistency.

• Redo is written to the redo log buffer (and eventually to redo log files) for crash recovery.

7. Any row-level or statement-level triggers are fired according to the DML logic (e.g., AFTER INSERT FOR EACH ROW).

8. Oracle enforces constraints (e.g., NOT NULL, UNIQUE, FOREIGN KEY).

9. Indexes are updated immediately for the inserted rows.

10. When the user issues a COMMIT, Oracle

• Writes a commit record to the redo logs.

• Marks the undo as no longer needed (eventually reusable).

• Leaves the data in the buffer cache for DBWR to write to disk asynchronously.

Direct Path Loads

When a direct path load is used, Oracle bypasses the buffer cache and appends rows directly into new blocks on disk, above the high water mark. Here are the high-level steps:

1. Oracle locates the relevant data blocks (empty or partially full).

2. Data is inserted directly into data files (bypassing the buffer cache).

3. No undo is generated for the data insert (limited undo/redo is recorded for crash recovery).

4. Row-level triggers are not supported, so if present, Oracle will default back to a conventional insert. However, statement-level triggers are supported.

5. Some constraints are enforced immediately (e.g., NOT NULL), while others are deferred until the insert completes or until the commit, depending on configuration.

6. Indexes are not updated immediately but are maintained automatically after the insert. They remain usable and do not need to be rebuilt.

7. Once committed, the commit record is written to the redo log files and the direct insert is completed.

Using the APPEND and APPEND_VALUES hint

Example of APPEND

The following query is an example of how the APPEND hint works.  It also includes an example query plan

INSERT /*+ APPEND */ 

INTO sales_archive

SELECT 

  sale_id, 

  sale_date, 

  amount

FROM sales_source

WHERE sale_date < TO_DATE('2025-01-01', 'YYYY-MM-DD');

Example Plan for APPEND

-----------------------------------------------------------------------------------------

| Id  | Operation                | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT         |               | 10000 | 300K  |   100   (1)| 00:00:01 |

|   1 |  LOAD AS SELECT (APPEND) | SALES_ARCHIVE |       |       |            |          |

| * 2 | INDEX RANGE SCAN          | IDX_SALE_DATE | 10000 | 300K  |    30   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("SALE_DATE"<TO_DATE('2025-01-01','YYYY-MM-DD'))

Explanation of the Plan:

• Operation 0: (INSERT STATEMENT): The root operation.

• Operation 1: (LOAD AS SELECT (APPEND)): Direct-path insert into sales_archive.

• Operation 2: (INDEX RANGE SCAN): Oracle uses the idx_sale_date index to retrieve rows where sale_date < '2025-01-01'

• Predicate Information: The access predicate indicates the index is used to filter rows based on the sale_date condition

Example of APPEND_VALUES

INSERT /*+ APPEND_VALUES */ 

INTO sales_archive (

sale_id, 

sale_date, 

amount)

VALUES 

(1001,TO_DATE('2024-12-31', 'YYYY-MM-DD'),500),

(1002, TO_DATE('2024-12-31', 'YYYY-MM-DD'), 750);

Example Plan for APPEND_VALUES

-----------------------------------------------------------------------------------------

| Id  | Operation                | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT         |               |   2   | 300K  |   100   (1)| 00:00:01 |

|   1 |  LOAD TABLE(APPEND)      | SALES_ARCHIVE |       |       |            |          |

-----------------------------------------------------------------------------------------

Notes: 

• Its important to note that in all of these examples, explain plans in the real world are often much more complex.  Also, using the hint does not guarantee the optimizer will chooses these paths.  The optimizer will still look at the overall cost to decide which plan is more optimal.  And of course, test, test, test!!

• If you use the wrong append hint in the wrong conects, e.g. APPEND_VALUES when you have a sub-query, or APPEND when using an insert values syntx, then the hints will be ignored and a conventional insert will be performed.

NOAPPEND Hint

The NOAPPEND hint tells the optimizer to use a conventional insert.  Useful in some parallel query scenarios

Further Reading

• You can find more information on using the hint in the documentation 

Published on 

Published 22nd April 2025