You are loading a large amount of data into a table and want to insert rows in a faster way, you can get this in a easy way using features of Oracle, of course, you issue more or less commit statements too.
- Using table’s logging attribute, set to nolloging, it reduce the generation redo for direct path (this feature no apply to regular DML). Please be carefull about backup implications.
- Using a direct path feature, such as the following;
- hint /*+ append */ on queries that use a subquery like source data. In 11.1 there is a BUG (8595132) that makes it works with the VALUES clause but isn’t correct.
- hint /*+ append_values */ on queries that use the VALUES clause.
- Using CTAS, CREATE TABLE <TAB1> AS SELECT.
HTH – Antonio NAVARRO