Saturday, July 31, 2010

How to insert large amount of data in oracle table faster way

How to insert large amount of data in oracle table faster way
===========================================

For this can be Use Direct Load Insert
-------------------------------------------------------
Direct Load Insert is a faster way of running an INSERT statement.Direct Load Insert
differs from Conventional Insert in that it bypasses the buffer cache.

To use Direct Load Insert, add the APPEND hint to your INSERT statement. like below.....

INSERT /*+ APPEND*/
INTO target_table
SELECT * FROM source_table

Need to know for Direct load Insert
---------------------------------------------------
1. Data is appended to the table. Existing free space is not re-used.
2. Direct Load Insert only works for INSERT INTO .. SELECT .... Inserts
3. Direct Load Insert uses rollback segments to maintain indexes as the data is loaded
4. Direct Load Insert can be run with a NOLOGGING option making it even faster.
5. Direct Load Insert locks the table in exclusive mode. No other session can insert, update, or delete data, or maintain any indexes.
6. Referential Integrity (Foreign Key) constraints and triggers must be disabled before running Direct Path Insert
7. Direct Load Insert cannot occur on:
o Index Organised Tables
o Tables with LOB Columns
o Tables with Object Columns
o Clustered Tables


DEMO
=====


SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 31 12:28:13 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn basel2@test107
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL> set timing on
SQL> set time on
12:28:41 SQL>
12:28:42 SQL>
12:28:42 SQL> create table direct_load_insert as select * from TABLE_OF_VIEW_BACKUP
12:30:57 2 where brancd=0 ;

Table created.

Elapsed: 00:00:04.03
12:31:28 SQL>
12:31:31 SQL> desc direct_load_insert
Name Null? Type
----------------------------------------- -------- ----------------------------
BRANCD VARCHAR2(3)
ACTYPE VARCHAR2(3)
ACTNUM VARCHAR2(12)
CURBAL NUMBER
LONCON VARCHAR2(3)
ACTTIT VARCHAR2(60)
CUSCOD VARCHAR2(10)
SHDESC VARCHAR2(10)
OPNDAT DATE
EXPDAT DATE
SECURITY_BAL NUMBER
SECURITY_TYPE VARCHAR2(4000)
VALDAT DATE
CATGRY VARCHAR2(1)
VALPRD NUMBER(4)
LCAAMT NUMBER(16,3)
REMAMT NUMBER(16,3)
BANCOD VARCHAR2(3)
CMPIND VARCHAR2(1)
REMARK VARCHAR2(50)

12:31:48 SQL>
12:31:50 SQL>
12:31:51 SQL> INSERT /*+ APPEND*/
12:32:19 2 INTO direct_load_insert
12:32:44 3 SELECT * FROM TABLE_OF_VIEW_BACKUP ;

3706656 rows created.

Elapsed: 00:01:13.31
12:34:17 SQL>
12:34:23 SQL>
12:34:23 SQL>
12:34:23 SQL> ROLLBACK ;

Rollback complete.

Elapsed: 00:00:00.06
12:34:53 SQL>
12:34:54 SQL>
12:34:54 SQL> INSERT INTO direct_load_insert
12:35:31 2 SELECT * FROM TABLE_OF_VIEW_BACKUP ;

3706656 rows created.

Elapsed: 00:01:53.36
12:37:30 SQL>
12:38:06 SQL>
12:38:06 SQL> ROLLBACK
12:39:44 2 /

Rollback complete.

Elapsed: 00:01:25.35
12:41:12 SQL>
12:41:14 SQL>
12:41:14 SQL> DROP TABLE direct_load_insert ;

Table dropped.

Elapsed: 00:00:28.17
12:41:57 SQL>
12:41:59 SQL>
12:41:59 SQL>