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>
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
-
4096 Columns1 week ago
-
-
-
-
-
-
-
Oracle Cloud & Third party tools4 years ago
-
-
-
Moving Sideways8 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment