| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> bulk, is it as important even when there are only a dozen of row.
Hi i recently read an good article, sorry I don't remember where,
but there said even when there is a dozen or records is a good idea to use
bulk
I didn't trust to much (only a dozen of rows?) I always thought bulk was
for hundreds of rows at least
I did a test, and after running twice every one(to be sure the difference is
not for disk reads)
setting timing on I got without bulk 10 and with bulk 0, this really
impressed me .
The rule seems to be, ALWAYS use bulk, unless you have 5 record or less.
Looking at tom's books "ffective by design" in a test the difference are
impressive,
using bulk 5M gets instead 37M.
Do you agree with these?
ALWAYS use bulk, unless you have 5 record or less
SQL> DECLARE
2 TYPE TEST IS TABLE OF VARCHAR2(20);
3 TEST1 test;
4 i number := 0;
5 cursor c is SELECT LQF_CUENTA FROM CTALIQFON;
6 test2 c%rowtype;
7 BEGIN
8 open c;
9 loop
10 fetch c into test2;
11 -- for i in 1..test2.count loop
12 dbms_output.put_line(test2.lqf_cuenta);
13 -- end loop;
14 exit when c%notfound;
15 end loop;
16 END;
17 /
812.02.2.03 814.02.2.01 814.02.2.02 814.02.2.03 812.02.2.04 851.99.1.98 851.99.2.98 851.99.1.97 851.99.3.98 851.99.2.97 851.99.3.97 812.02.2.01 812.02.2.02 814.02.2.04 814.02.2.04
Procedimiento PL/SQL terminado con Úxito.
real: 10
SQL> DECLARE
2 TYPE TEST IS TABLE OF VARCHAR2(20);
3 TEST1 test;
4 i number := 0;
5 cursor c is SELECT LQF_CUENTA FROM CTALIQFON;
6 BEGIN
7 open c;
8 loop
9 fetch c bulk collect into test1;
10 for i in 1..test1.count loop
11 dbms_output.put_line(test1(i));
12 end loop;
13 exit when c%notfound;
14 end loop;
15 END;
16 /
812.02.2.03 814.02.2.01 814.02.2.02 814.02.2.03 812.02.2.04 851.99.1.98 851.99.2.98 851.99.1.97 851.99.3.98 851.99.2.97 851.99.3.97 812.02.2.01 812.02.2.02 814.02.2.04
Procedimiento PL/SQL terminado con Úxito.
real: 0
 |
 |