Insert SQL statement performance in Oracle

Increase speed of Insert operation & Maximizing Data-Loading Speeds in Oracle

You can increase the speed of Insert SQL statement operation in Oracle.
1. Disable the archive for the table by changing the logging attribute to NOLOGGING.
Disadvantage
of using NOLOGGING option is difficult to manage replication and recovery process of table.
2. Use DIRECT PATH Load operation. When we choose 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. It will increase insert operation performance instead of looking space in-between table. Direct path disadvantage table consuming a large amount of disk space.

Disable the logging if no need for archive

-- Check the logging mode
select table_name, logging from user_tables where table_name = 'EMP';
TABLE_NAME LOG
---------- ---
EMP YES

-- Disable the logging of table:
SQL> alter table emp nologging;

Use the DIRECT PATH operation
Use APPEND hint for direct load operation. Following are the ways:
1. INSERT /*+ APPEND */ on queries that use a subquery for determining which records are
inserted
2. INSERT /*+ APPEND_VALUES */ on queries that use a VALUES clause
3. CREATE TABLE…AS SELECT

Example of 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.

Error
After insert operation please commit otherwise it will give the following error on Select statement:
ORA-12838 error indicating that direct path loaded data must be committed before it is selected.

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.

Disadvantage of NOLOGGING:
Difficult to recover the data in case of corruption or data loss from the table.

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

Note: 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 )

Connecting to %s

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