Bulk binding accelerates the efficiency of SQL statements by binding a complete set at a time and processing data in arrays within the PL/SQL block. Two DML statements are used: bulk collect and forall. Bulk collect-used to improve query (select) Performance forall-used to improve (insert, delete, update) performance. International practice: Describe the test environment: Windows 2000 + Oracle9i hardware environment CPU 1.8g + Ram 512 M step 1. create Table T3 (PID Number (20), pname varchar2 (50); Step 2. let's test (10 million rows) insert operation 1. loop declaretype idtab is table of number (20) index by binary_integer; Type nametab is table of varchar2 (50) index by binary_integer; PID idtab; pname nametab; begin for J in 1 .. 100000 loop PID (j): = J; pname (j): = 'name is '| to_char (j); en D loop; for I in 1 .. 100000 loop insert into T3 values (PID (I), pname (I); End loop; end; -- takes 6.391 S 2. use foralldeclaretype idtab is table of number (20) index by binary_integer; Type nametab is table of varchar2 (50) index by binary_integer; PID idtab; pname nametab; begin for J in 1 .. 100000 loop PID (j): = J; pname (j): = 'name is '| to_char (j); End loop; forall I in 1 .. 100000 insert into T3 values (PID (I ), Pname (I); end; -- the performance difference between 0.719 S is too obvious ~~ Step 3. let's test (million rows) insert operation 1. loop-79.532 s elapsed 2. using forall -- takes 274.056 S and we find that using forall is slower than loop 3. use forall to test 50 W -- time consumed 10.322 S 4. use forall to test 80 W -- 30.901 S 5. using forall to test 80 W -- 90.333 S is not difficult to understand now, because the size of batch execution data will also have a significant impact on the results, the array is too large, occupying the cache space, with the addition of additional overhead, my testing environment is weak and the problem is reflected. Step 4. solution: batch operation: a total of 10 declaretype idtab is table of number (20) index by binary_integer; Type nametab is table of varchar2 (50) index by binary_integer; PID idtab; pname nametab; n_count number; begin for J in 1 .. 1000000 loop PID (j): = J; pname (j): = 'name is '| to_char (j); End loop; n_count: = 100000; loop exit when n_count> 1000000; forall I in (n_count-100000 + 1 ).. n_count insert into T3 va Lues (PID (I), pname (I); n_count: = n_count + 100000; end loop; end; -- takes 10.045 s -- batch operation, pens each time, it takes 7.609 S-batch operation, 1 W each time, 6.412 S-batch operation, 1 K each time, it takes seconds to see that we do not need to go through multiple tests to obtain a suitable base. * Usage of bulk collect 1. set serveroutput on; declaretype idtab is table of t3.pid % Type in select into; Type nametab is table of t3.pname % type; PID idtab; pname nametab; beginselect PID, pname bulk collect into PID, pname from T3; for I in PID. first .. 1000 loop dbms_output.put_line (PID (I) | '-' | pname (I); End loop; end;/2. in fetch into, declaretype idtab is table of t3.pid % type; Type nametab is table of t3.pname % type; PIDs idtab; pnames nametab; cursor C1 is select PID, pname from T3; beginopen C1; fetch C1 bulk collect into PIDs, pnames; for I in PIDs. first .. 1000 loop dbms_output.put_line (PIDS (I) | '-' | pnames (I); End loop; close C1; end ;/
* References
Http://search.itpub.net/search.php? S = Hangzhou & Action = showresults & searchid = 392429 & sortby = lastpost & sortorder = descendings = Hangzhou & Action = showresults & searchid = 392429 & sortby = lastpost & sortorder = descending
