Using fetch bulk collect into to read cursor data in batch in Oracle

Usually we obtain cursor data in the form of fetch some_cursor into var1 and var2. When there are not many records in the cursor, It is not tight. However, since Oracle 8i, Oracle has provided fetch bulk collect to fetch data in the cursor in batches. It can improve the efficiency when reading a large amount of data in the cursor, just like in SNMP protocol, V2 adds get-bulk PDU to V1, it is also used to obtain node values on devices in batches more efficiently.

The format of fetch bulk collect into is: Fetch some_cursor collect into col1, col2 limit XXX. Col1 and col2 are declared set type variables. XXX is the size of each data block (number of records), which is equivalent to the size of the buffer. You can leave the limit XXX size unspecified. The following uses an actual example to describe how to use it and compares it with the execution efficiency of the fetch into record one by one. The test environment is Oracle 10g 10.2.1.0. The queried contact table sr_contacts contains 1802983 records, and rownum is used in the cursor to limit the number of returned records.

Use fetch bulk collect into get cursor data

Declare

-- Declare the collection type and variable, and declare the type with reference to the Field Type

Type id_type is table of sr_contacts.sr_contact_id % type;
V_id id_type;

Type phone_type is table of sr_contacts.contact_phone % type;
V_phone phone_type;

Type remark_type is table of sr_contacts.remark % type;
V_remark remark_type;

 

 

Cursor all_contacts_cur is -- use rownum to limit the number of retrieved records.

Select sr_contact_id, contact_phone, remark from sr_contacts where rownum <= 100000;

Begin

Open all_contacts_cur;
Loop
Fetch all_contacts_cur bulk collect into v_id, v_phone, v_remark limit 256;
For I in 1 .. v_id.count loop -- traverses the set
-- Use v_id (I)/v_phone (I)/v_remark (I) to retrieve the field value to execute your business logic
End loop;
Exit when all_contacts_cur % notfound; -- exit cannot be followed by Fetch, otherwise the record will be missed
End loop;
Close all_contacts_cur;
End;
Declare

 

-- Declare the collection type and variable, and declare the type with reference to the Field Type

Type id_type is table of sr_contacts.sr_contact_id % type;
V_id id_type;

Type phone_type is table of sr_contacts.contact_phone % type;
V_phone phone_type;

Type remark_type is table of sr_contacts.remark % type;
V_remark remark_type;

 

 

Cursor all_contacts_cur is -- use rownum to limit the number of retrieved records.

Select sr_contact_id, contact_phone, remark from sr_contacts where rownum <= 100000;

Begin
Open all_contacts_cur;
Loop
Fetch all_contacts_cur bulk collect into v_id, v_phone, v_remark limit 256;
For I in 1 .. v_id.count loop -- traverses the set
-- Use v_id (I)/v_phone (I)/v_remark (I) to retrieve the field value to execute your business logic
End loop;
Exit when all_contacts_cur % notfound; -- exit cannot be followed by Fetch, otherwise the record will be missed
End loop;
Close all_contacts_cur;
End;

Use fetch into to get cursor data row by row

Declare

-- Declare the variable and declare the type with reference to the type of the field

V_id sr_contacts.sr_contact_id % type;
V_phone sr_contacts.contact_phone % type;
V_remark sr_contacts.remark % type;

 

Cursor all_contacts_cur is -- use rownum to limit the number of retrieved records.

Select sr_contact_id, contact_phone, remark from sr_contacts where rownum <= 100000;

Begin

Open all_contacts_cur;
Loop
Fetch all_contacts_cur into v_id, v_phone, v_remark;
Exit when all_contacts_cur % notfound;
-- Use v_id/v_phone/v_remark to retrieve the field value to execute your business logic
NULL; -- only one null operation is placed here, which is only used to test the efficiency of number acquisition in the cycle.
End loop;
Close all_contacts_cur;
End;
Declare

V_id sr_contacts.sr_contact_id % type;
V_phone sr_contacts.contact_phone % type;
V_remark sr_contacts.remark % type;

Cursor all_contacts_cur is -- use rownum to limit the number of retrieved records.

Select sr_contact_id, contact_phone, remark from sr_contacts where rownum <= 100000;

Begin

Open all_contacts_cur;
Loop
Fetch all_contacts_cur into v_id, v_phone, v_remark;
Exit when all_contacts_cur % notfound;
-- Use v_id/v_phone/v_remark to retrieve the field value to execute your business logic
NULL; -- only one null operation is placed here, which is only used to test the efficiency of number acquisition in the cycle.
End loop;
Close all_contacts_cur;
End;

Performance Comparison

Take a look at the test results, the number of seconds consumed for each execution of five times:

When rownum <= 100000:

Fetch bulk collect into: 0.125 seconds, 0.125 seconds, 0.125 seconds, 0.125 seconds, 0.141 seconds

Fetch into time: 1.266 seconds, 1.250 seconds, 1.250 seconds, 1.250 seconds, 1.250 seconds

When rownum <= 1000000:

Fetch bulk collect into: 1.157 seconds, 1.157 seconds, 1.156 seconds, 1.156 seconds, 1.171 seconds

Fetch into time: 12.128 seconds, 12.125 seconds, 12.125 seconds, 12.109 seconds, 12.141 seconds

When rownum <= 10000:

Fetch bulk collect into: 0.031 seconds, 0.031 seconds, 0.016 seconds, 0.015 seconds, 0.015 seconds

Fetch into time: 0.141 seconds, 0.140 seconds, 0.125 seconds, 0.141 seconds, 0.125 seconds

When rownum <= 1000:

Fetch bulk collect into: 0.016 seconds, 0.015 seconds, 0.016 seconds, 0.016 seconds, 0.015 seconds

Fetch into time: 0.016 seconds, 0.031 seconds, 0.031 seconds, 0.032 seconds, 0.015 seconds

From the test results, the efficiency of using fetch bulk collect into is clearly displayed when the number of records of the cursor is larger, which is almost the same as that of the hour.

Note: No. A set is defined for each query column before fetch bulk collect into, which is cumbersome. We may have used the % rowtype type of the table before, Similarly We can also define the set type of the table's % rowtype. Let's look at the following example. In this example, we use the first and last attributes of the Set instead of the count attribute for traversal.

Declare

-- Declare the collection type and variable, and declare the type with reference to the Field Type

Type contacts_type is table of sr_contacts % rowtype;
V_contacts contacts_type;

 

Cursor all_contacts_cur is -- use rownum to limit the number of retrieved records.

Select * From sr_contacts where rownum <= 10000;

Begin

Open all_contacts_cur;
Loop
Fetch all_contacts_cur bulk collect into v_contacts limit 256;
For I in v_contacts.first .. v_contacts.last loop -- traverse the set
-- V_contacts (I). sr_contact_id/v_contacts (I). contact_phone/v_contacts (I). remark
-- To extract the value of each field to execute your business logic
End loop;
Exit when all_contacts_cur % notfound;
End loop;
Close all_contacts_cur;
End;
Declare

-- Declare the collection type and variable, and declare the type with reference to the Field Type

Type contacts_type is table of sr_contacts % rowtype;
V_contacts contacts_type;

 

Cursor all_contacts_cur is -- use rownum to limit the number of retrieved records.

Select * From sr_contacts where rownum <= 10000;

Begin

Open all_contacts_cur;
Loop
Fetch all_contacts_cur bulk collect into v_contacts limit 256;
For I in v_contacts.first .. v_contacts.last loop -- traverse the set
-- V_contacts (I). sr_contact_id/v_contacts (I). contact_phone/v_contacts (I). remark
-- To extract the value of each field to execute your business logic
End loop;
Exit when all_contacts_cur % notfound;
End loop;
Close all_contacts_cur;
End;

Limit Parameters

You can adjust the limit parameter Size Based on your actual needs to achieve your optimal performance. The limit parameter affects the usage of PGA. You can also omit the limit parameter in fetch bulk and write it

Fetch all_contacts_cur bulk collect into v_contacts;

In some documents, if the limit parameter is not specified, the arraysize parameter value of the database is used as the default value. Use show arraysize in sqlplus. The default value is 15. Set arraysize 256 can be used to change the value. In fact, when the limit parameter is not included in the test, the outer loop executes only one round. It seems that it is not limit 15. Therefore, when the limit parameter is not included, the outer loop can be removed. The begin-end part can be written:

 

Begin
Open all_contacts_cur;
Fetch all_contacts_cur bulk collect into v_contacts;
For I in v_contacts.first .. v_contacts.last loop -- traverse the set
-- V_contacts (I). sr_contact_id/v_contacts (I). contact_phone/v_contacts (I). remark
-- To extract the value of each field to execute your business logic
NULL; -- only one null operation is placed here, which is only used to test the efficiency of number acquisition in the cycle.
Dbms_output.put_line (2000 );
End loop;
Close all_contacts_cur;
End;
Begin
Open all_contacts_cur;
Fetch all_contacts_cur bulk collect into v_contacts;
For I in v_contacts.first .. v_contacts.last loop -- traverse the set
-- V_contacts (I). sr_contact_id/v_contacts (I). contact_phone/v_contacts (I). remark
-- To extract the value of each field to execute your business logic
NULL; -- only one null operation is placed here, which is only used to test the efficiency of number acquisition in the cycle.
Dbms_output.put_line (2000 );
End loop;
Close all_contacts_cur;
End;

Other usage of bulk collect (always for set)

In the select into statement, for example:

Select sr_contact_id, contact_phone bulk collect into v_id, v_phone
From sr_contacts where rownum <= 100;
Dbms_output.put_line ('count: '| v_id.count |', first: '| v_id (1) |' | v_phone (1 ));

 

In the returning into statement, for example:

Delete from sr_contacts where sr_contact_id <30
Returning sr_contact_id, contact_phone bulk collect into v_id, v_phone;
Dbms_output.put_line ('count: '| v_id.count |', first: '| v_id (1) |' | v_phone (1 ));

Forall's bulk DML operation, which is much better than the operation after the for Set

Fetch all_contacts_cur bulk collect into v_contacts;
Forall I in 1 .. v_contacts.count
-- Forall I in v_contacts.first... v_contacts.last
-- Forall I in indices of v_contacts -- 10 Gb or more, can be a non-continuous set
Insert into sr_contacts (sr_contact_id, contact_phone, remark)
Values (v_contacts (I). sr_contact_id, v_contacts (I). contact_phone, v_contacts (I). remark );

-- Or a single delete/update operation