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 with different methods.
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 with different methods.
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;