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.
If your looking for low-hanging fruit that can be plucked to improve your Oracle database management, the APPEND hint is a logical place to start. 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 allows 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.
