Oracle-insert Performance Optimization

It took a long time to see a friend importing data! There are actually a lot of quick methods to work out!

There are many ways to insert data into a table, but the performance varies greatly due to different methods.

---- 1. original statement
Drop table t1 purge;

Create table t1
(
Sid number,
Sname varchar2 (20)
) Tablespace test;

 

Create or replace procedure proc01
(
Sname varchar2
)
As
Begin
For I in 10000000
Loop
Execute immediate
'Insert into t1 (sid, sname) values ('| I |', ''' | sname | ''')';
Commit;
End loop;
End;
/

 

Alter system flush shared_pool;

Set timing on;

Exec proc01 ('oppyangtest ');

Used time: 02: 02: 54.12

 

 

---- 2. bind variables

 

Drop table t1 purge;

Create table t1
(
Sid number,
Sname varchar2 (20)
) Tablespace test;

Create or replace procedure proc02
(
Sname varchar2
)
As
Begin
For I in 10000000
Loop
Execute immediate
'Insert into t1 (sid, sname) values (: no' | ', ''' | sname | ''')' using I;
Commit;
End loop;
End;
/

 

Alter system flush shared_pool;

Set timing on;

Exec proc02 ('oppyangtest ');

Used time: 00: 22: 59.79

Select count (*) from t1;

---- 3. Static statements

 

Drop table t1 purge;

Create table t1
(
Sid number,
Sname varchar2 (20)
) Tablespace test;

 

Create or replace procedure proc03
As
Begin
For I in 10000000
Loop

Insert into t1 values (I, 'oppyangtest ');
Commit;
End loop;
End;
/

Alter system flush shared_pool;

Set timing on;

Exec proc03;

Used time: 00: 20: 42.42

Select count (*) from t1;

 

 

---- 4. Batch submission

Drop table t1 purge;

Create table t1
(
Sid number,
Sname varchar2 (20)
) Tablespace test;

 

Create or replace procedure proc04
As
Begin
For I in 10000000
Loop
Insert into t1 values (I, 'oppyangtest ');
End loop;
Commit;
End;
/

Alter system flush shared_pool;

Set timing on;

Exec proc04;

Used time: 00: 11: 48.42

 

---- 5. Set

Drop table t2 purge;

Create table t2
(
Sid number,
Sname varchar2 (20)
) Tablespace test;

 

Alter system flush shared_pool;

Select count (*) from t1;

Set timing on;

 

Insert into t2 select sid, sname from t1;

Used time: 00: 01: 02.18

Commit;

Select count (*) from t2;

 

---- 6. Set + append

Drop table t2 purge;

Create table t2
(
Sid number,
Sname varchar2 (20)
) Tablespace test;

 

Alter system flush shared_pool;

Select count (*) from t1;

 

Set timing on;

 

Insert/* + append */into t2 select sid, sname from t1;

Used time: 00: 00: 36.94

Commit;

Select count (*) from t2;

 

 

---- 7. Set + append + nologging

Drop table t2 purge;

Create table t2
(
Sid number,
Sname varchar2 (20)
) Nologging tablespace test;

 

Alter system flush shared_pool;

Select count (*) from t1;

 

 

Set timing on;

Insert/* + append */into t2 select sid, sname from t1;

Used time: 00: 00: 35.07

Commit;

Select count (*) from t2;

 

 

---- 8. Data Loading 1

Drop table t2 purge;

 

Alter system flush shared_pool;

Select count (*) from t1;

 

Set timing on;

 

Create table t2
As
Select sid, sname from t1;

Used time: 00: 00: 25.91

 

Select count (*) from t2;

 

 

---- 9. Data Loading 2

Drop table t2 purge;

 

Alter system flush shared_pool;

Select count (*) from t1;

 

Set timing on;

 

Create table t2 nologging
As
Select sid, sname from t1;

Used time: 00: 00: 04.89

 

Select count (*) from t2;

 

---- 10. Data Loading + Parallelism

Drop table t2 purge;

 

Alter system flush shared_pool;

Select count (*) from t1;

 

Set timing on;

 

Create table t2 nologging parallel 24 -- Based on your server
As
Select sid, sname from t1;

Used time: 00: 00: 02.89

Select count (*) from t2;