bulk, is it as important even when there are only a dozen of row.
- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 10 Mar 2004 15:55:14 -0400
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
-- Sorry for posting so frequenly, but I am trying to reach your level :)
-- When I get it, I'll ask less frequently
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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » bulk, is it as important even when there are only a dozen of row.
