# Oracle RDBMS Optimizer Hint: Boost INSERT Statements With APPEND_VALUES

> Date: August 16, 2017 | Author: Yaniv Yehuda | Category: Database DevOps

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 […]

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.

When Should You Use APPEND_VALUES Instead of APPEND in Oracle?
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.
What Is the Oracle APPEND_VALUES Hint and How Does It Work?
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.
How Does APPEND_VALUES Affect Oracle Database Performance and Structure?
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.
What Are the Best Practices for Using APPEND_VALUES to Optimize Oracle INSERT Performance?
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
What’s the difference between APPEND and APPEND_VALUES in Oracle?APPEND applies to INSERT INTO ... SELECT, while APPEND_VALUES works for single-row INSERT INTO ... VALUES statements, both using direct path inserts for faster performance.

When should I use the APPEND_VALUES hint?Use APPEND_VALUES when inserting multiple rows in loops or PL/SQL blocks to boost performance without rewriting logic for bulk inserts.

Can APPEND_VALUES cause fragmentation?It reduces fragmentation in most cases by allocating new blocks, but regular monitoring and indexing are still best practices.

How does DBmaestro help optimize Oracle performance?DBmaestro automates database releases, enforces change control, and helps manage schema evolution, keeping performance improvements consistent and compliant.

## End-to-end database CI/CD, governed and audited.

See how DBmaestro accelerates your release pipeline while keeping every change secure.

[Book a demo](https://www.dbmaestro.com/schedule-demo/) | [Guided Tour](https://demo.dbmaestro.com/demo/cmdeiq2j900lvvm0ibx1296yt)

## Related Articles

### [Db2 DevOps: Automate Releases Without Losing DBA Control](https://www.dbmaestro.com/blog/database-devops/db2-devops-automate-releases/)

Your Db2 DevOps team already runs one of the most disciplined change processes in the enterprise. This series is about making it count for more. Ask a good barista how they pull an espresso and they’ll talk about grind, dose, temperature and timing. Ask a Db2 team how they release a change and you get […]

### [Stateful Is the New DevOps Fault Line](https://www.dbmaestro.com/blog/database-devops/stateful-is-the-new-devops-fault-line/)

DevOps has transformed how enterprises build and deliver software, but the stateful database remains its hardest problem. Teams package applications into containers. They increasingly define infrastructure as code. CI/CD pipelines automate testing and deployment. When something goes wrong, teams can often replace an artifact, redeploy a previous version, or recreate an environment. But one part […]

### [From Provisioning to Control: The New Database DevOps Reality](https://www.dbmaestro.com/blog/database-devops/new-database-devops-reality/)

New Database DevOps is entering a new reality, and serverless databases are becoming the next programmable infrastructure. That was my biggest takeaway from IBM's announcement of the Db2 Serverless technical preview. At first glance, it looks like any modern cloud database: instant provisioning, automatic scaling, pay-per-use, and no infrastructure to manage. But the more I […]

---
Canonical URL: https://www.dbmaestro.com/blog/database-devops/boost-insert-statements/
