What You’ll Learn:

• The difference between the Oracle APPEND and APPEND_VALUES optimizer hints and when to use each.
• How the APPEND_VALUES hint can improve performance in INSERT statements with individual rows.
• Practical examples of using the APPEND_VALUES hint in PL/SQL for faster data insertion.
• Best practices for optimizing Oracle database performance with direct path INSERT operations.

One of the most common methods to improve Oracle INSERT performance is the APPEND optimizer hint. APPEND triggers a direct path INSERT, appending data above the table’s high-water mark and bypassing free space scanning in existing blocks.

However, the APPEND hint can only be applied to 

INSERT INTO … SELECT statements, not single-row INSERT INTO … VALUES statements. That’s where the APPEND_VALUES hint comes in, delivering the same performance benefits for individual row insertions.

Unfortunately, use of the APPEND hint is only possible when performing an INSERT using a SELECT clause, which inserts the SELECT statement into the table. APPEND cannot be used to insert single rows via the INSERT statement with the VALUES clause.

Oracle introduced the APPEND_VALUES optimizer hint to solve a key limitation of APPEND: the inability to optimize INSERT VALUES operations.

If you regularly perform single-row or loop-based inserts in PL/SQL, APPEND_VALUES allows you to still leverage direct path INSERT performance.

Example use case:
When inserting large datasets with a FORALL loop, adding the hint can significantly boost execution speed:

FORALL i IN table_type.FIRST..table_type.LAST
INSERT /*+ APPEND_VALUES */ INTO table_name VALUES
table_type(i);

Pro Tip: If you’re dealing with bulk INSERT operations inside loops, consider using FORALL with the APPEND_VALUES hint to reduce context switches between PL/SQL and SQL engines, leading to faster execution.

The use of the APPEND_VALUES optimizer hint can drastically increase INSERT statement performance when inserting a large number of rows into a table, especially when the table has many holes (blocks that have empty spaces which should be filled during the row insertion process).

Pro Tip: When using the APPEND_VALUES hint, ensure that your table has proper indexing and is free from frequent fragmentation to maximize the performance benefits. This can help prevent performance degradation in the long term.

The APPEND_VALUES hint directs Oracle to allocate new data blocks above the table’s high-water mark, reducing fragmentation and eliminating the need to search for free space in existing blocks.

This not only speeds up individual inserts but also improves long-term table structure and performance consistency.

Best Practice:
Pair performance-focused optimizations like APPEND_VALUES with robust version control and change management. Use solutions like DBmaestro to ensure all schema and data changes are tracked, preventing version drift as your database evolves.

To fully benefit from APPEND_VALUES, DBAs should combine optimizer hints with a controlled, automated release process. This ensures performance gains don’t come at the cost of database consistency.

Best Practices:

• Always maintain source control for database changes.
  
• Use release automation tools like DBmaestro to manage updates across environments.
  
• Regularly monitor table fragmentation and re-index as needed.
  
• Benchmark performance after applying hints to ensure measurable improvement.
  

Mastering Oracle’s optimizer hints, including APPEND_VALUES, enables faster development cycles and better-performing systems, especially when supported by structured DevOps practices.

Mastering the full array of Oracle Database’s optimizer hints is a great way to accelerate your pace of development. But only with an appropriate oversight and control system in place, can those development changes be promoted through to release. It’s like pen and paper. On their own, each is of little utility, but together, they can raise empires.

Key Takeaways

• APPEND_VALUES delivers direct-path INSERT performance for individual rows, similar to APPEND for bulk inserts.
• It allocates new blocks above the high-water mark, minimizing fragmentation.
• Using FORALL + APPEND_VALUES reduces PL/SQL context switching for bulk inserts.
• Combine performance tuning with source control and database release automation to prevent drift and errors.

FAQs