DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

SBOMs are essential to circumventing software supply chain attacks, and they provide visibility into various software components.

Related

  • Decoding Database Speed: Essential Server Resources and Their Impact
  • Understanding Time Series Databases
  • Exploring Data Redaction Enhancements in Oracle Database 23ai
  • The Rise of the Intelligent AI Agent: Revolutionizing Database Management With Agentic DBA

Trending

  • How Predictive Analytics Became a Key Enabler for the Future of QA
  • Understanding Time Series Databases
  • Zero-Trust AI: Applying Cybersecurity Best Practices to AI Model Development
  • MCP and The Spin-Off CoT Pattern: How AI Agents Really Use Tools
  1. DZone
  2. Data Engineering
  3. Databases
  4. Oracle RDBMS Optimizer Hint: Boost INSERT Statements With APPEND_VALUES

Oracle RDBMS Optimizer Hint: Boost INSERT Statements With APPEND_VALUES

In this post, we take a look at how you can improve your INSERT statements' performance with this simple trick.

By 
Yaniv Yehuda user avatar
Yaniv Yehuda
·
Sep. 14, 17 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
7.3K Views

Join the DZone community and get the full member experience.

Join For Free

One of the most common ways to improve the performance of an INSERT operation is to use the APPEND optimizer hint.

APPEND forces the optimizer to perform a direct path INSERT and appends new values above the high water mark (the end of the table) while new blocks are being allocated. This is instead of the default process whereby holes in your blocks are filled with free space. In other words, APPEND enhances the performance of the INSERT statement.

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.

Those Who Cannot APPEND, APPEND_VALUES

A few years back, Oracle introduced a little-known optimizer hint called APPEND_VALUES. This hint provides the same feature and behavior of the direct path INSERT, but when calling on the VALUES clause, users can still insert new individual records into the table and benefit from the APPEND feature.

Consider the case when using a PL/SQL block and adding a large number of records in a FORALL loop, while the loop is doing an INSERT VALUES statement. In this case, the APPEND_VALUES optimizer hint can simply be added to the INSERT statement to deliver performance improvements similar to those offered by the APPEND hint.

For example:

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

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).

APPEND_VALUES in the Broader Context of Oracle Database 

This hint forces the Oracle database to allocate new blocks above the table’s high water mark, with new rows inserted into the new allocated area, instead of searching for free space in other existing blocks.

Making use of this hint results not just in a better-structured database but will also allow users to insert and append individual rows – making for a more fluid (read more agile) development process.

Of course, the easier it is to make changes, the more important it is to have a well-structured and enforced change documentation process. The more agile you become, the more "small," off the cuff changes you'll make and the more those "small changes" will contribute to large-scale version drift. 

Oracle DBAs and developers would be wise to implement some sort of enforced source control. With a sufficiently robust source control solution in place, DBAs can focus more on database improvement and less on process control and management. Changes will be blocked if performed out-of-process and all valid changes will be automatically documented. 

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.

Database Boost (C++ libraries)

Published at DZone with permission of Yaniv Yehuda, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Decoding Database Speed: Essential Server Resources and Their Impact
  • Understanding Time Series Databases
  • Exploring Data Redaction Enhancements in Oracle Database 23ai
  • The Rise of the Intelligent AI Agent: Revolutionizing Database Management With Agentic DBA

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • [email protected]

Let's be friends: