Updating multiple columns using forall
In the next section, methods for handling sparse collections available in Oracle 10g are presented.
This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr.
All procedural code is handled by the PL/SQL engine while all SQL is handled by the SQL statement executor, or SQL engine.
There is an overhead associated with each context switch between the two engines.
Since the script uses separate collections for each bind, it is suitable for all versions of Oracle that support bulk operations.
l_id_tab t_id_tab := t_id_tab(); l_code_tab t_code_tab := t_code_tab(); l_start NUMBER; l_size NUMBER := 10000; BEGIN -- Populate collections. l_size LOOP l_id_tab.extend; l_code_tab.extend; The performance of the bulk delete is similar to the performance of the bulk update; the bulk operation is approximately twice the speed of the conventional operation.
SQL courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.
I am trying to bulk update 5 rows each contains 7 columns with different values using For All and patch functions.Bulk binds can improve the performance when loading collections from a queries.The SET SERVEROUTPUT ON DECLARE TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE; l_tab t_bulk_collect_test_tab := t_bulk_collect_test_tab(); l_start NUMBER; BEGIN -- Time a regular population.The forall_test table is used to compare the performance of individual updates against bulk updates using the update_script listed below.Notice the use of the ROW keyword in the bulk operation.