Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> bulk, is it as important even when there are only a dozen of row.

bulk, is it as important even when there are only a dozen of row.

From: Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Wed, 10 Mar 2004 15:55:14 -0400
Message-ID: <002901c406d9$9d0d9740$2501a8c0@dazasoftware.com>


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



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Wed Mar 10 2004 - 13:56:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US