Can INSERT’s be tuned?



The CREATE TABLE … AS SELECT statement (CTAS) is a powerful tool for manipulating large sets of data. CTAS provides a mechanism for efficiently executing a SQL query and storing the results of that query in a new database table.

The INSERT /*+APPEND*/ … AS SELECT statement offers the same capabilities with existing database tables.

APPEND into Tables

By using the APPEND hint, you ensure that Oracle always grabs “fresh” data blocks by raising the high-water-mark for the table. If you are doing parallel insert DML, the Append mode is the default and you don’t need to specify an APPEND hint.

When you use the APPEND hint for INSERT, data is simply appended to a table above the HWM which has the effect of not creating UNDO. Existing free space in blocks is not used.

High Water Mark

The high water mark is the…

View original post 407 more words