FORALL and bulk collect statements for Oracle databases

FORALL and bulk collect statements for Oracle databases

Let's take a look at the execution process of PL/SQL blocks: When the PL/SQL runtime engine processes a piece of code, it uses the PL/SQL engine to execute Procedural Code, the SQL statement is sent to the SQL engine for execution. After the SQL engine completes execution, the result is returned to the PL/SQL engine. This kind of interaction between PL/SQL engine and SQL engine is called context switch ). Each exchange brings additional costs.

 

1. FORALL is used to enhance the exchange between PL/SQL engines and SQL engines.
2. Bulk collect is used to enhance the exchange between SQL engines and PL/SQL engines. (We have already introduced it)

1. FORALL Introduction

With FORALL, multiple DML can be batch sent to the SQL engine for execution, minimizing the overhead of context interaction. The following is one of FORALL:

Syntax:

 

1 FORALL index_name IN 2 {lower_bound .. upper_bound3 | INDICES