When using the FORALL statement, I like to feed the index variable with the INDICES OF clause, rather than using the lower_bound .. upper_bound option.
INDICES OF is perfect when the corresponding collection may be sparse, but I prefer using it also when the collection is dense. In my opinion, this is a more elegant structure, self documented, and the use of one pattern throughout the system makes the code more consistent and easier to maintain.
But is this only a question of personal preference?
Is the difference between these two options only syntactic?
In other words, is this:
SQL> truncate table t;
Table truncated.
SQL> declare
2 v_collection sys.odcinumberlist;
3 begin
4 insert into t (x,y)
5 select rownum,rownum from dual connect by level <= 10;
6
7 v_collection := sys.odcinumberlist(1,7,43);
8 forall i in 1 .. v_collection.count
9 update t
10 set y = 0
11 where x = v_collection(i);
12 dbms_output.put_line(sql%rowcount || ' rows updated');
13 end;
14 /
2 rows updated
PL/SQL procedure successfully completed.
completely identical to this:
SQL> truncate table t;
Table truncated.
SQL> declare
2 v_collection sys.odcinumberlist;
3 begin
4 insert into t (x,y)
5 select rownum,rownum from dual connect by level <= 10;
6
7 v_collection := sys.odcinumberlist(1,7,43);
8 forall i in indices of v_collection
9 update t
10 set y = 0
11 where x = v_collection(i);
12 dbms_output.put_line(sql%rowcount || ' rows updated');
13 end;
14 /
2 rows updated
PL/SQL procedure successfully completed.
?
In most cases the answer is yes, but a few days ago I found a case where the behavior is different between the two options.
I was wondering what happens exactly when the collection is empty.
This is not a hypothetical question. Many times we populate a collection from a query, and then use this collection in a FORALL statement to perform some DML statement. It is legit that the query will return no records, and therefore the collection will be empty.
I did some tracing and found out that in the following example, when using INDICES OF, there is a context switch from the PL/SQL engine to the SQL engine – the UPDATE statement is executed, and since the collection is empty no binding happens and no rows are affeceted.
v_collection := sys.odcinumberlist();
forall i in indices of v_collection
update t
set y = 0
where x = v_collection(i);
=====================
PARSING IN CURSOR #1808232998640 len=33 dep=1 uid=154 oct=6 lid=154 tim=153168435007 hv=2619660526 ad='7ffeeeaf44d0' sqlid='7n7dshqf29q7f'
UPDATE T SET Y = 0 WHERE X = :B1
END OF STMT
PARSE #1808232998640:c=0,e=126,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=153168435007
=====================
...
EXEC #1808232998640:c=0,e=2893,p=0,cr=7,cu=0,mis=1,r=0,dep=1,og=1,plh=931696821,tim=153168437995
CLOSE #1808232998640:c=0,e=1,dep=1,type=3,tim=153168438053
While in the following example, when using the lower_bound..upper_bound syntax, there is no context switch. The PL/SQL engine does an optimization here – it understands that going from 1 to 0 yields no items, and it doesn’t bother calling the SQL engine.
v_collection := sys.odcinumberlist();
forall i in 1 .. v_collection.count
update t
set y = 0
where x = v_collection(i);
The outcome is the same – nothing had to be updated anyway. From the performance perspective, the lower_bound..upper_bound option spared a context switch, so it’s probably better (I didn’t measure it). I assume it’s negligible in most cases (unless you make lots of calls with empty collections).
But there may be also consequences to this optimization.
Here is one such consequence I was thinking about.
Look at this example, and see if you find any problem with it:
truncate table t;
declare
v_collection sys.odcinumberlist;
begin
insert into t (x,y)
select rownum,rownum from dual connect by level <= 10;
v_collection := sys.odcinumberlist();
forall i in indices of v_collection
update t
set y = 0
where x = v_collection(i);
dbms_output.put_line(sql%rowcount || ' rows updated');
end;
/
truncate table t;
declare
v_collection sys.odcinumberlist;
begin
insert into t (x,y)
select rownum,rownum from dual connect by level <= 10;
v_collection := sys.odcinumberlist();
forall i in 1 .. v_collection.count
update t
set y = 0
where x = v_collection(i);
dbms_output.put_line(sql%rowcount || ' rows updated');
end;
/
What do you expect to see as the output of each case?
Here is the output when using INDICES OF:
SQL> declare
2 v_collection sys.odcinumberlist;
3 begin
4 insert into t (x,y)
5 select rownum,rownum from dual connect by level <= 10;
6
7 v_collection := sys.odcinumberlist();
8 forall i in indices of v_collection
9 update t
10 set y = 0
11 where x = v_collection(i);
12 dbms_output.put_line(sql%rowcount || ' rows updated');
13 end;
14 /
0 rows updated
PL/SQL procedure successfully completed.
As expected, 0 rows updated.
Now, what happens with the second anonymous block?
SQL> declare
2 v_collection sys.odcinumberlist;
3 begin
4 insert into t (x,y)
5 select rownum,rownum from dual connect by level <= 10;
6
7 v_collection := sys.odcinumberlist();
8 forall i in 1 .. v_collection.count
9 update t
10 set y = 0
11 where x = v_collection(i);
12 dbms_output.put_line(sql%rowcount || ' rows updated');
13 end;
14 /
10 rows updated
PL/SQL procedure successfully completed.
10 rows updated!
SQL%ROWCOUNT returns the number of affected rows from the latest executed SQL statement. The FORALL statement did not execute the UPDATE statement, so the latest SQL statement that was actually executed is the INSERT statement.
This can lead to confusing situations and potential bugs.
So now I have one more reason to prefer INDICES OF 🙂
