blog

/
08Jan2014

Oracle Ace Ami Aharonovich: New Optimizer Hint: APPEND_VALUES for Improving Insert Performance

  • 5 Tags
  • 0 Comments

One of the most common ways to improve the performance of an INSERT operation is to use the optimizer hint called APPEND. APPEND forces the optimizer to perform a direct path INSERT and append new values above the high water mark (the end of the table) while new blocks are being allocated, instead of filling the holes in blocks with free space. This will significantly enhance the performance of the INSERT statement. However, the use of the APPEND hint is only possible when performing an INSERT using a SELECT clause, which will insert the SELECT statement into the table. APPEND cannot be used to insert single rows via the INSERT statement with the VALUES clause.


Oracle Database 11g R2 introduces a new optimizer hint called APPEND_VALUES. This new hint provides the same feature and behavior of the direct path INSERT as described above (using the SELECT statement). However, when using the INSERT statement with 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, for significant performance improvements similar to those achieved when using the APPEND hint with the INSERT SELECT statement.
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). This new hint will force 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.


For more information regarding this feature, please read here



eBOOK - in database automation we trust


About the Author

Ami Aharonovich

Ami Aharonovich, Oracle ACE & OCP, Founder & CEO of DBAces, President of ilOUG (Israel Oracle User Group) and head of Oracle department at John Bryce Training

Feel free to send your questions and feedback to Ami Aharonovich at Ami@DBAces.co.il.

COMMENTS

Leave a Reply

Your email address will not be published. Required fields are marked *