Talking about Pl/sql batch processing statement: BULK Collect and ForAll's contribution to optimization _oracle

We know that running SQL statements in a PL/SQL program is expensive because the SQL statement is committed to the SQL engine processing
This control transfer between the Pl/sql engine and the SQL engine is called the context but instead, it has an extra overhead each time it changes.

Take a look at the picture below:

However, forall and bulk collect can allow the Pl/sql engine to compress multiple contexts into one, which makes it much less time-consuming to execute an SQL statement that handles multiple-line records in Pl/sql
Please look at the following picture again:

Here is a detailed explanation of the two

㈠ through Bulk COLLECT acceleration query

The use of ⑴bulk COLLECT

With bulk collect, the query results can be loaded into the collections at once, instead of being processed by a single piece of cursor
You can use bulk in the select INTO, fetch into, returning into statement COLLECT
Note that when using bulk collect, all into variables must be collections

Give a few simple examples:

① uses bulk in the SELECT INTO statement collect

Copy Code code as follows:

DECLARE
TYPE Sallist is TABLE of Employees.salary%type;
Sals sallist;
BEGIN
SELECT salary BULK COLLECT into sals from employees where rownum<=50;
--then use the data in the collection
End;
/

② uses bulk in fetch into collect

Copy Code code as follows:

DECLARE
TYPE Deptrectab is TABLE of Departments%rowtype;
Dept_recs Deptrectab;
CURSOR cur is SELECT department_id,department_name from departments where department_id>10;
BEGIN
OPEN cur;
FETCH cur BULK COLLECT into dept_recs;
--then use the data in the collection
End;
/

③ uses bulk in returning into collect

Copy Code code as follows:

CREATE TABLE EMP as SELECT * FROM employees;

DECLARE
TYPE Numlist is TABLE of Employees.employee_id%type;
Enums numlist;
TYPE NameList is TABLE of Employees.last_name%type;
Names NameList;
BEGIN
DELETE EMP WHERE department_id=30
Returning Employee_id,last_name BULK COLLECT into Enums,names;
Dbms_output. Put_Line (' Deleted ' | | sql%rowcount| | ' Rows: ');
For I in enums. A.. Enums. Last
LOOP
Dbms_output. Put_Line (' employee# ' | | Enums (i) | | ': ' | | Names (i));
End LOOP;
End;
/

Deleted6rows:
employee#114:raphaely
Employee#115:khoo
Employee#116:baida
Employee#117:tobias
Employee#118:himuro
Employee#119:colmenares

Eate table EMP as SELECT * from Employees;declare TYPE numlist is table of Employees.employee_id%type; Enums numlist; TYPE NameList is TABLE of Employees.last_name%type; Names NameList; BEGIN DELETE EMP WHERE department_id=30 returning employee_id,last_name BULK COLLECT into Enums,names; Dbms_output. Put_Line (' Deleted ' | | sql%rowcount| | ' Rows: '); For I in enums. A.. Enums. Last LOOP Dbms_output. Put_Line (' employee# ' | | Enums (i) | | ': ' | | Names (i)); End LOOP; end;/deleted6rows:employee#114:raphaelyemployee#115:khooemployee#116:baidaemployee#117:tobiasemployee#118: Himuroemployee#119:colmenares

Optimization of ⑵bulk COLLECT for large data Delete update

Here's the delete, update.

For example:
You need to delete 10 million rows of data in a large table of 100 million rows
Requirements are done with the least impact on other applications of the database, with the fastest speed

If the business cannot stop, you can refer to the following ideas:
According to rowID slicing, reuse rowid sorting, batch processing, back table deletion
When the business can not stop, choose this way, is indeed the best
You can generally control the submission within every 10,000 lines without causing too much pressure on the rollback segment
When I'm doing big DML, I usually choose one thousand or two thousand lines a commit
Choose to do when the business low peak, not too much impact on the application
The code is as follows:

Copy Code code as follows:

DECLARE
--cursor sorted by ROWID
--the deletion condition is oo=xx, this need according to the actual situation to decide
CURSOR MyCursor is SELECT rowid the from T WHERE oo=xx the order by ROWID;
TYPE Rowid_table_type is table of ROWID index by Pls_integer;
V_rowid Rowid_table_type;
BEGIN
OPEN MyCursor;
LOOP
FETCH mycursor BULK COLLECT into V_rowid LIMIT 5000;--5000 row Submit once
EXIT when v_rowid.count=0;
ForAll i in V_rowid. A.. V_rowid. Last
DELETE T WHERE rowid=v_rowid (i);
COMMIT;
End LOOP;
Close MyCursor;
End;
/

⑶ Limit the number of records extracted by bulk COLLECT

Grammar:
FETCH cursor BULK COLLECT into ... [LIMIT rows];
Where rows can be constants, or the result of a variable or evaluation is an expression of an integer

Suppose you need to query and process 1W rows of data, you can use bulk collect to remove all rows at once and then populate them in a very large collection.
However, this method consumes a large number of Pga,app for the session and may cause performance degradation due to the PGA swap page

At this point, the limit clause is useful, and it helps us control how much memory is used to process the data

Example:

Copy Code code as follows:

DECLARE
CURSOR Allrows_cur is SELECT * FROM employees;
TYPE Employee_aat is TABLE of Allrows_cur%rowtype INDEX by Binary_integer;
V_emp Employee_aat;
BEGIN
OPEN allrows_cur;
LOOP
Fetch allrows_cur BULK fetch into v_emp LIMIT 100;

/* Process the data through the scanning set * *
For I in 1. V_emp.count
LOOP
Upgrade_employee_status (V_emp (i). employee_id);
End LOOP;

EXIT when Allrows_cur%notfound;
End LOOP;

Close allrows_cur;
End;
/

⑷ Batch fetch multiple columns

Requirements:
Extract traffic in the transportation table that is less than 20 km/RMB the details
code is as follows:

Copy code code: