This post is the latest in an occasional series on the theme of stuff that doesn’t work quite how I thought it did.
It is the result of finding out the fun way that, rather than being “much faster” than using a humble Cursor For Loop , Bulk Collect/Forall for DML in PL/SQL can merely be a bit less slow.
Just in case my boss is reading this, I’d better get my excuses in right at the beginning.
This is what the Oracle PL/SQL Language Reference has to say about Forall :
A FORALL
statement is usually much faster than an equivalent FOR
LOOP
statement. However, a FOR
LOOP
statement can contain multiple DML statements, while a FORALL
statement can contain only one. The batch of DML statements that a FORALL
statement sends to SQL differ only in their VALUES
and WHERE
clauses. The values in those clauses must come from existing, populated collections.
…and later in the same document ….
The FORALL
statement runs one DML statement multiple times, with different values in the VALUES
and WHERE
clauses.
The different values come from existing, populated collections or host arrays. The FORALL
statement is usually much faster than an equivalent FOR
LOOP
statement.
I’ll be testing that assertion in this post.
Using a simple test case, which involves performing different DML operations on different tables using a single record set, I’ll begin by comparing the relative performance of:
- simple SQL statements inside a PL/SQL block
- Cursor For Loops
- Bulk Collect/Forall operations
I’ll then explore the potential performance gains available using database objects such as VARRAYs and Global Temporary Tables to hold the array being used in the DML.
NOTE – If you want to run these tests yourself on your own environment/database version to validate these findings, you can find the scripts I’ve used here on my Github Repo.
Continue reading “FORALL DML – why context isn’t everything”Like this:
Like Loading...