Suppose we have a simple table t(x int). In order to insert 1000 rows into it, what is the most efficent way?
Notes:
- This test is based on Effective Oracle by Design, Tom Kyte, pp 249-251
- Environment, Oracle 10gR2 on Windows
Summary:
Method 1 is the worst
Method 4 is the best
Method 2 and 3 tie
Method 1 - without bind variables
begin
for i in 1..1000
loop
execute immediate 'insert into t values (' i ')';
end loop;
end;
/
From tkprof:
********************************************************************************
insert into t values (1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 21 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 1 21 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (recursive depth: 1)
********************************************************************************
insert into t values (2)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 3 1
...
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1029 2.54 2.72 0 0 22 0
Execute 1044 0.35 0.37 0 1001 3038 1000
Fetch 138 0.04 1.04 65 336 0 104
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2211 2.95 4.15 65 1337 3060 1104
Misses in library cache during parse: 1013
Misses in library cache during execute: 13
Method 2 - Using binding variables
begin
for i in 1..1000
loop
execute immediate 'insert into t values ( :x )' using i ;
end loop;
end;
/
insert into t values ( :x )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000 0.18 0.16 0 2 1041 1000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1001 0.18 0.16 0 2 1041 1000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 1002 0.20 0.17 0 2 1041 1000
Fetch 2 0.00 0.00 0 8 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1007 0.20 0.18 0 10 1041 1001
Misses in library cache during parse: 3
Misses in library cache during execute: 3
Method 3 using statis SQL
begin
for i in 1..1000
loop
insert into t values (i) ;
end loop;
end;
/
From tkprof:
INSERT INTO T
VALUES
(:B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000 0.20 0.16 0 2 1041 1000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1001 0.20 0.16 0 2 1041 1000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 1002 0.20 0.17 0 2 1041 1000
Fetch 3 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1008 0.20 0.18 0 6 1041 1001
Method 4 - FORALL
declare
l_data dbms_sql.number_table;
l_empty dbms_sql.number_table;
begin
for i in 1 .. 1000
loop
l_data(mod(i,100)) := i;
if ( mod (i,100 ) = 0 )
then
forall j in 0 .. l_data.count-1
insert into t values ( l_data(j));
l_data :=l_empty;
end if;
end loop;
end;
/
From tkprof:
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 12 0.03 0.01 0 3 38 1000
Fetch 3 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 0.03 0.01 0 7 38 1001
For more info about bulk binding see:
http://www.dba-oracle.com/oracle_news/2004_1_31_plsqlL_bulk_binds_FORALL.htm
BULK BINDING
- Collection
- BULK COLLECT
- FORALL
No comments:
Post a Comment