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
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
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

Migrate, Modernize and Build Java Web Apps on Azure: This live workshop will cover methods to enhance Java application development workflow.

Modern Digital Website Security: Prepare to face any form of malicious web activity and enable your sites to optimally serve your customers.

Kubernetes in the Enterprise: The latest expert insights on scaling, serverless, Kubernetes-powered AI, cluster security, FinOps, and more.

A Guide to Continuous Integration and Deployment: Learn the fundamentals and understand the use of CI/CD in your apps.

Related

  • Accelerating Insights With Couchbase Columnar
  • Best Methods To Backup and Restore Database in SQL Server
  • Demystifying Databases, Data Warehouses, Data Lakes, and Data Lake Houses
  • Why SQL Isn’t the Right Fit for Graph Databases

Trending

  • DDD and Microservices
  • 5 Steps To Tame Unplanned Work
  • Ways To Reduce JVM Docker Image Size
  • Data Ingestion for Batch/Near Real-Time Analytics
  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.

Yaniv Yehuda user avatar by
Yaniv Yehuda
·
Sep. 14, 17 · Tutorial
Like (2)
Save
Tweet
Share
6.7K 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

  • Accelerating Insights With Couchbase Columnar
  • Best Methods To Backup and Restore Database in SQL Server
  • Demystifying Databases, Data Warehouses, Data Lakes, and Data Lake Houses
  • Why SQL Isn’t the Right Fit for Graph Databases

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • 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
  • support@dzone.com

Let's be friends: