In batch runs to make a massive data dump into the same table using an INSERT or UPDATE for register within a block, the process can be optimized with the use of parameters (if client supports it) or if we use ODBC with bind variables.
Recall the steps taken by Oracle to process a query:
1) Sintactic Validation
2) Semantic Validation
3) Optimization
4) Generation of the QEP (Query Execution Plan)
5) Implementation of the QEP (Query Execution Plan)
Sentences can pick up the parameters by value (where salary > 1000) or once the sentence is compiled using Bind Variables (where salary>: b1). The advantage of the second option is that Oracle compile the sentence only one-time and reuses the compiled code for each of the values for the parameters.
But we must be aware because in the latter case because Oracle can't calculate the degree of selectivity of a query and, instead, apply a degree of selectivity by default (associated with each type of operation), which can give in wrong decisions.
Therefore, is inadvisable to use Bind Variables if you work with costs, except that we work with sentences to be executed repeatedly and not offering many doubts as to the possible access plans.
In proven cases like this, the execution time is reduced by up to 90% on an insert of 100.000 records ...
Creating the necessary table for testing:
create table TEST ( NUM number (22), TEXT varchar (100) ); Loading parameters by value:
declare v_i number;
begin
loop
INSERT INTO TEST VALUES (3, '50');
v_i := v_i + 1;
exit when v_i > 1000000;
end loop;
rollback;
end; Loading parameters using bind variables
declare v_i number;
begin
loop
execute immediate 'INSERT INTO TEST VALUES (:x, :y)' using 3, '50';
v_i := v_i + 1;
exit when v_i > 1000000;
end loop;
rollback;
end; I enclose the test results before applying changes in production (on a Friday afternoon 
)
| ROWS | BY VALUE | WITH BIND VARIABLES |
| 10000 | 5,3350 | 0,4370 |
| 100000 | 58,5160 | 6,1000 |
| 1000000 | (A)570,1060 | (B)54,1950 |
