Oracle uses an optimizer to determine the most efficient way to perform each SQL command. For INSERT statements, Oracle tries to insert each new record into an existing block of data already allocated to the table. This execution plan optimizes the use of space required to store the data. However, it may not provide adequate performance for an INSERT with a SELECT command that inserts multiple rows. You can improve the execution plan by using the APPEND hint to improve the performance of large inserts. The APPEND hint tells the database to find the last block into which the table’s data has ever been inserted. The new records are then inserted starting in the first block of the new extent allocated above the table’s high watermark (HWM), which will be explained shortly. Furthermore, the inserted data is written directly to the datafiles, bypassing the data block buffer cache. As a result, there is much less space management work for the database to do during the INSERT, which means the INSERT may complete faster when the APPEND hint is used.
You specify the APPEND hint within the INSERT command. A hint looks like a comment—it starts with /* and ends with */. The only difference is that the starting set of characters includes a plus sign (+) before the name of the hint (but no spaces between the asterisk and the plus sign, otherwise the hint is ignored). The following example shows an INSERT command whose data is appended to the table (above the HWM):
The records from the BOOK_ORDER table will be inserted into the BOOKSHELF table. Instead of attempting to reuse space within the BOOKSHELF table, the new records will be placed at the end of the table’s physical storage space.
Because the new records will not attempt to reuse available space that the table has already used, the space requirements for the BOOKSHELF table may increase. In general, you should use the APPEND hint only when inserting large volumes of data into tables with little reusable space. The point at which appended records are inserted is called the table’s high watermark, or HWM—and the only way to reset the high watermark is to TRUNCATE the table. Because TRUNCATE deletes all records and cannot be rolled back, you should make sure you have a backup of the table’s data prior to performing the TRUNCATE. See TRUNCATE in the Alphabetical Reference for further details.
Leave a Reply