Two engines are used when Oracle run PL / SQL, all procedural codes are done by PL / SQL Engine, and all SQL is processed by SQL ENGINE. So if Oracle performs the same DML operation from a Collection, in order to avoid two sets of ENGINE switches consumed system resources, Bulk Binds can be used to complete all DML operations BINDING to one operation. This will greatly improve the efficiency of PL / SQL. The following is a simple test, in two ways to insert 100,000 data, you can see the efficiency of about 7 times.
Code: ------------------------------------------------ -------------------------------- SQL> CREATE TABLE TEST1 (2 ID Number (10), 3 Description Varchar2 (50 ));
Table created
SQL> ALTER TABLE TEST1 Add (2 ConsTRAINT TEST1_PK PRIMARY Key (ID));
Table altered
SQL> SET TIMING ON;
SQL> DECLARE 2 TYPE id_type IS TABLE OF test1.id% TYPE; 3 TYPE description_type IS TABLE OF test1.description% TYPE; 4 5 t_id id_type: = id_type (); 6 t_description description_type: = description_type (); 7 BEGIN 8 FOR i in 1 .. 100000 loop 9 t_id.extend; 10 t_description.extend; 11 12 T_ID (t_id.last): = i; 13 t_description (t_description.last): = 'Description:' || to_Char (i); 14 End loop; 15 16 for i in t_id.first .. t_id.last loop 17 INSERT INTO TEST1 (ID, Description) 18 VALUES (T_ID (i), T_Description (I)); 19 end loop; 20 21 COMMIT; 22 End ; twenty three /
PL / SQL Procedure SuccessFully Completed
Executed in 141.233 Seconds
SQL> TRUNCATE TABLE TEST1;
Table truncated
Executed in 0.631 seconds
SQL> SQL> DECLARE 2 TYPE id_type IS TABLE OF test1.id% TYPE; 3 TYPE description_type IS TABLE OF test1.description% TYPE; 4 5 t_id id_type: = id_type (); 6 t_description description_type: = description_type (); 7 BEGIN 850 loop 9 t_id.extend; 10 t_description.extend; 11 12 t_id (t_id.last): = i; 13 t_description (t_description.last): = 'description:' || to_Char (i) 14 end loop; 15 16 forall I in t_id.first .. t_id.last 17 INSERT INTO TEST1 (ID, Description) 18 VALUES (T_ID (i), T_Description (i)); 19 20 COMMIT; 21 End; 22 / PL / SQL Procedure SuccessFully Completed
Executed in 27.52 seconds
SQL> SELECT Count (*) from test1;
COUNT (*) ---------- 100000
Executed in 0.04 seconds
SQL>
Below we use the 100,000 data plugged in the above example to test the power of Bulk Collect.
Code: ------------------------------------------------ -------------------------------- SQL> SET TIMING ON; SQL> SQL> DECLARE 2 TYPE ID_TYPE IS TABLE OF TEST1 .id% TYPE; 3 TYPE description_type IS TABLE OF test1.description% TYPE; 4 5 t_id id_type: = id_type (); 6 t_description description_type: = description_type (); 7 8 CURSOR c_data IS 9 SELECT * 10 FROM test1; 11 BEGIN 12 for cur_recin c_data loop 13 t_id.extend; 14 t_description.extend; 15 16 T_ID (t_id.last): = cur_rec.id; 17 t_description (t_description.last): = cur_rec.description; 18 end loop; 19 end; 20 /
PL / SQL Procedure SuccessFully Completed
Executed in 2.974 seconds
SQL> SQL> DECLARE 2 TYPE id_type IS TABLE OF test1.id% TYPE; 3 TYPE description_type IS TABLE OF test1.description% TYPE; 4 5 t_id id_type; 6 t_description description_type; 7 BEGIN 8 SELECT id, description 9 BULK COLLECT INTO t_id , t_description from test1; 10 end; 11 / pl / sql procedure successful mixed
Executed in 0.371 seconds
SQL> ------------------------------------------------- --------------------------------
Conclusion: When we need to put a lot of retrieval results into a Collection, use Bulking will be more effective than using the Cursor loop.
