It works by permitting the process to insert the data directly into new data blocks at the end of the object, rather than using space currently allocated to the object. So the new data is appended to the table. This is much faster because it permits Oracle to by-pass some of the processes that occur for each row inserted, such as index maintenance which only occurs when the insert is committed. Significantly, the hint also reduces the volume of redo written.
Every time a piece of data is changed in an Oracle database, the new data is written to the redo logs. This information can then be used to reapply changes to a database during media recovery, or it can be applied to a standby database as a part of a disaster recover solution (Oracle call this technique 'Physical DataGuard'). Every change, even to working storage tables and including indexes, generates redo information.
The reduction in redo can be very significant in systems using DataGuard across a WAN. Bandwidth constraints can cause a backlog of redo information waiting to be transmitted. In most systems the majority of redo is generated by overnight batch processes and it can take time during the day for that backlog to clear and for the DR system to catch up with production. Hence, reducing redo is important.
Therefore the APPEND hint should be used in insert statements when both of the following criteria are met:
- The table has been created or truncated prior to the insert. It is not sufficient to delete all the rows because the empty blocks remain allocated to the table.
- The table should be populated in a single monolithic insert statement. Multiple statements should be combined using the UNION ALL operator between the queries. Otherwise most of the benefit will be lost during multiple index maintenance phases.
The session statistics show the savings in a number of areas.
Fewer blocks are changed because they are not written and then rewritten as each row is inserted. Table blocks are only written once during the insert and the index is only maintained when the insert is committed.
- When using the APPEND hint, the insert should be committed immediately. It must be committed before the table can be referenced, otherwise an error will be generated.
- The data in the table will have been completely replaced by this point, so it is almost certainly appropriate to refresh the optimiser statistics. In Applicaition Engine use the %UpdateStats MetaSQL.
- The APPEND hint cannot be used in conjuction with the %InsertSelect MetaSQL because the hint MUST be placed between the 'insert' and 'into' keywords. NB: The statement will not fail if you get the hint wrong.