Increase Insert Operation Performance in Oracle

 

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
inserted

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’;

TABLE_NAME LOG
———- —
EMP YES

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.