Insert operation increase performance or Maximizing Data-Loading Speeds
Set the table’s logging attribute to NOLOGGING; this minimizes the generation redo for direct path operations
INSERT /*+ APPEND */ on queries that use a subquery for determining which records are
INSERT /*+ APPEND_VALUES */ on queries that use a VALUES clause
CREATE TABLE…AS SELECT
select table_name, logging from user_tables where table_name = ‘EMP’;
To disable the logging of table:
SQL> alter table emp nologging;
example uses a direct path INSERT statement to load data into the table:
insert /*+APPEND */ into emp (first_name) select username from all_users;
direct path operations such as INSERT
/*+APPEND */ combined with NOLOGGING generate a minimal amount of redo.
After insert operation please commit otherwise it will give the following error:
ORA-12838 error indicating that direct path loaded data must be committed before it is selected.
Note: When you direct path insert into a table, Oracle will insert the new rows above the high-water mark. Even if there is ample space freed up via a DELETE statement, when direct path inserting, Oracle will always load data above the high-water mark.
Result: table consuming a large amount of disk space.
two performance advantages over regular insert statements:
• If NOLOGGING is specified, then a minimal amount of redo is generated.
• The buffer cache is bypassed and data is loaded directly into the datafiles. This can
significantly improve the loading performance.
SQL> select * from emp;
This indicates that there is logical corruption in the datafile:
ORA-01578: ORACLE data block corrupted (file # 4, block # 147)
ORA-01110: data file 4: ‘/u01/dbfile/O12C/users01.dbf’
ORA-26040: Data block was loaded using the NOLOGGING option
As the prior output indicates, the data in the table is unrecoverable. Use NOLOGGING only in situations where the data isn’t critical or in scenarios where you can back up the data soon after it was created.