Category: SQL

Mixed Feelings About MERGE

I have a love-hate relationship with the MERGE SQL statement.
Well, maybe it’s a bit harsh. Let’s make it a like-dislike relationship.

Why I Like It

Because it provides an elegant way to perform an “insert if not exists, update if exists” logic in a single statement. It has a rich syntax with various options for different use cases. One of the very first posts I wrote in this blog, in 2013, was a primer of the MERGE statement.

Why I Dislike It

I don’t like how it deals with concurrent transactions that try to handle the same key.
Let me show you what I mean exactly.

Demo Setup

I have a table t, with a primary key on the id column.

 23ai> create table t ( 2 id integer not null 3 constraint t_pk primary key, 4 data varchar2(100), 5 last_operation varchar2(10) 6 ); Table created. 23ai> 23ai> insert into t (id,data) 2 select rownum,'init' 3 from dual 4 connect by level<=5; 5 rows created. 23ai> commit; Commit complete.

I want a procedure that gets two input parameters – p_id and p_data – and either inserts a new record into t (if there is no record with that id already in the table) or updates an existing record (if there is a record with that id already).
I will write two procedures – one that uses the MERGE statement and one that uses the INSERT and UPDATE statements.

 23ai> create procedure with_merge ( 2 p_id in t.id%type, 3 p_data in t.data%type 4 ) as 5 begin 6 merge into t 7 using dual 8 on (t.id = p_id) 9 when matched then 10 update set data = p_data, 11 last_operation = 'update' 12 when not matched then 13 insert (id,data,last_operation) 14 values (p_id,p_data,'insert'); 15 end with_merge; 16 / Procedure created. 23ai> 23ai> create procedure without_merge ( 2 p_id in t.id%type, 3 p_data in t.data%type 4 ) as 5 begin 6 insert into t (id,data,last_operation) 7 values (p_id,p_data,'insert'); 8 exception 9 when dup_val_on_index then 10 update t 11 set t.data = p_data, 12 t.last_operation = 'update' 13 where t.id = p_id; 14 end without_merge; 15 / Procedure created.

In the without_merge procedure we try to insert a record into t. If we get the dup_val_on_index exception (which means that t_pk – the only unique index that we have – already contains an entry for the p_id value), then we update the existing record.

Note that this is a simple implementation that does not cover all the edge cases.

Comparison

I would expect both procedures to behave the same way, and most of the time they do:

 23ai> begin 2 with_merge(1,'with_merge'); 3 with_merge(6,'with_merge'); 4 without_merge(2,'without_merge'); 5 without_merge(7,'without_merge'); 6 commit; 7 end; 8 / PL/SQL procedure successfully completed. 23ai> select * from t order by id; ID DATA LAST_OPERA ---------- ------------------------------ ---------- 1 with_merge update 2 without_merge update 3 init 4 init 5 init 6 with_merge insert 7 without_merge insert 7 rows selected.

Everything looks good.

But now let’s compare what happens when two sessions try to add a new record with the same id.
First, let’s use the without_merge procedure.

 -- -- In session 1 -- 23ai> exec dbms_application_info.set_client_info('Session 1') PL/SQL procedure successfully completed. 23ai> exec without_merge(8,'without_merge in session 1') PL/SQL procedure successfully completed.

A new record was added, but we didn’t commit yet, so it is not visible to other sessions.

Now, from another session we call the procedure with the same id:

 -- -- In session 2 --  23ai> exec dbms_application_info.set_client_info('Session 2') PL/SQL procedure successfully completed. 23ai> exec without_merge(8,'without_merge in session 2')

Session 2 is now blocked by session 1, which locks the unique index entry for id 8. Session 2 waits to lock the same entry, as part of the INSERT statement.

 23ai> break on client_info skip 1 23ai> col client_info head "Client|Info" format a10 23ai> col type head "Type" format a4 23ai> col lmode head "Mode|held" format a4 23ai> col rmode head "Mode|req" format a4 23ai> col obj head "Obj or Tx" format a55 23ai> 23ai> select 2 s.client_info, 3 l.type, 4 decode (l.lmode, 1,'-',2,'RS',3,'RX',4,'S',5,'SRX',6,'X') lmode, 5 decode (l.request,1,'-',2,'RS',3,'RX',4,'S',5,'SRX',6,'X') rmode, 6 case 7 when l.type='TM' then (select o.owner||'.'||o.object_name from dba_objects o where o.object_id=l.id1) 8 when l.type='TX' then to_char(trunc(l.id1/power(2,16)))||'/'||to_char(bitand(l.id1,to_number('ffff','xxxx')))||'/'||l.id2 9 end obj 10 from 11 v$lock l, 12 v$session s 13 where 14 l.sid = s.sid and 15 s.username = USER and 16 l.type in ('TM','TX') 17 order by 18 1, 19 2; Client Mode Mode Info Type held req Obj or Tx ---------- ---- ---- ---- ------------------------------------------------------- Session 1 TM RX DEMO2.T TX X 3/23/91390 Session 2 TM RX DEMO2.T TX X 2/28/49946 TX S 3/23/91390

What will happen when session 1 commits?

 -- -- In session 1 -- 23ai> commit; Commit complete.

Once the transaction in session 1 is committed, the INSERT statement in session 2 fails due to the unique index, and the exception handler performs the UPDATE statement.

 -- -- In session 2 -- PL/SQL procedure successfully completed. 23ai> commit; Commit complete. 23ai> select * from t order by 1; ID DATA LAST_OPERA ---------- ------------------------------ ---------- 1 with_merge update 2 without_merge update 3 init 4 init 5 init 6 with_merge insert 7 without_merge insert 8 without_merge in session 2 update 8 rows selected.

This is the expected behavior. The final state is the same as if the two transactions were serialized (session 1 calls the procedure and commits, and only then session 2 calls the procedure).

Now let’s repeat the test with the MERGE version.

 -- -- In session 1 -- 23ai> exec with_merge(9,'with_merge in session 1') PL/SQL procedure successfully completed.

A new record was added, but we didn’t commit yet, so it is not visible to other sessions.

Now, from the other session we call the procedure with the same id:

 -- -- In session 2 --  23ai> exec with_merge(9,'with_merge in session 2')

Session 2 is now blocked by session 1. Same as in the previous test.

 Client Mode Mode Info Type held req Obj or Tx ---------- ---- ---- ---- ------------------------------------------------------- Session 1 TM RX DEMO2.T TX X 3/30/91369 Session 2 TM RX DEMO2.T TX X 2/33/49941 TX S 3/30/91369

What will happen when session 1 commits the transaction?

 -- -- In session 1 -- 23ai> commit; Commit complete.

Once the transaction in session 1 is committed, the INSERT statement in session 2 fails due to the unique index, but unlike in the without_merge case, the MERGE statement fails:

 -- -- In session 2 -- BEGIN with_merge(9,'with_merge in session 2'); END; * ERROR at line 1: ORA-00001: unique constraint (DEMO2.T_PK) violated on table DEMO2.T columns (ID) ORA-03301: (ORA-00001 details) row with column values (ID:9) already exists ORA-06512: at "DEMO2.WITH_MERGE", line 6 ORA-06512: at line 1 Help: https://docs.oracle.com/error-help/db/ora-00001/ 23ai> select * from t order by id; ID DATA LAST_OPERA ---------- ------------------------------ ---------- 1 with_merge update 2 without_merge update 3 init 4 init 5 init 6 with_merge insert 7 without_merge insert 8 without_merge in session 2 update 9 with_merge in session 1 insert 9 rows selected.

This is wrong, in my opinion.

This example demonstrates that the MERGE statement first of all checks if the record exists. If it doesn’t find such a record, it decides to perform an INSERT, and it will not change its mind even if the INSERT fails (as at this point it turns out there actually is an existing record).

Happy New Square Year

At a family dinner a few days ago, we realized that the upcoming year – 2025 – is a square number 🤩
Promptly the kids checked and announced that it’s the only year in the 21st century that is a square number.
We wondered how many square years there are per century over time, and, as always, such questions are answered using a simple SQL query.

with n as (select 30 centuries from dual), c as (select rownum century from n connect by level <= n.centuries), y as (select rownum*rownum year from n connect by level * level <= n.centuries*100), yc as (select y.year, to_number(to_char(to_date(y.year,'yyyy'),'cc')) century from y) select c.century, count(yc.year) number_of_sqaure_years, decode(count(yc.year), 1, any_value(yc.year)) the_only_sqaure_in_the_century from c, yc where yc.century(+) = c.century group by c.century order by c.century; CENTURY NUMBER_OF_SQAURE_YEARS THE_ONLY_SQAURE_IN_THE_CENTURY ---------- ---------------------- ------------------------------ 1 10 2 4 3 3 4 3 5 2 6 2 7 2 8 2 9 2 10 1 961 11 2 12 1 1156 13 2 14 1 1369 15 1 1444 16 2 17 1 1681 18 1 1764 19 1 1849 20 1 1936 21 1 2025 -- We Are Here! 22 1 2116 23 1 2209 24 1 2304 25 2 26 0 27 1 2601 28 1 2704 29 1 2809 30 1 2916 30 rows selected.

Looking at the first 30 centuries, the 10th century was the first to have only one square year. Since the 17th century, each century has exactly one square year, except for the 25th that has two and the 26th that has none.

Happy new year!

Optimization of NOT NULL Constraint Creation

Several years ago I wrote a series of 8 posts about constraint creation optimization. I think it’s time to add some more posts to the series.
I showed there, among other things, that Oracle does a nice optimization – that may save plenty of time – when we add in a single ALTER TABLE statement a new (nullable with no default value) column and a corresponding:
– inline (a.k.a. “column-level”) check constraint
or
– foreign key constraint (either inline or out-of-line)

What about NOT NULL constraints?

Oracle has a special optimization for the case of adding a column with a NOT NULL constraint (and no default value).
It checks if the table contains at least one record. If it does, then we cannot add the new column, becuase the NOT NULL constraint will be violated for all the existing rows. If the table doesn’t contain any records, the new column can be added.

Let’s start with an empty table: Continue reading “Optimization of NOT NULL Constraint Creation”

DECODE With Ranges

Tim Hall wrote this post yesterday (as part of Joel Kallman Day 2022) with some examples for “the old way” vs. “the new way” to do some stuff.
One of these examples is DECODE and CASE Expressions. As Tim wrote:

CASE expressions were first released in Oracle 8i […] Prior to that if we wanted something similar to a CASE expression in our SQL, we had to use the DECODE function.

This reminded me of a trick I came up with many years ago, probably before 8i 🙂

A table T has a column X, and we need a query that one of the items it returns is a rank – either “Low”, “Medium” or “High”. This rank is determined by the value of X and two thresholds – LOW_TH and HIGH_TH.

With a (Searched) CASE Expression it’s as simple as this: Continue reading “DECODE With Ranges”

Eurovision Analysis #JoelKallmanDay

Here’s my contribution to Joel Kallman Day 2022.

The picture is from the last time I met Joel. It was taken at ilOUG Tech Days 2020, just before an invigorating keynote by Joel.

Four years ago, as part of ODC Appreciation Day 2018 (as the annual Oracle community appreciation day was called that year), I wrote about Pattern Matching in SQL. That post was not a technical one.

This time I decided to return to the same topic, with a technical post.
I recorded a video that explains Pattern Matching by analyzing the winners of the Eurovision Song Contest over the years.

Pre-19c Crossedition Triggers

As shown in the previous post, finding all the crossedition triggers in the database is easy using DBA_TRIGGERS_AE, but this can be done only as of Oracle 19c – the release in which this data dictionary view was added.

Here are two options that can be used before 19c.

A Proper Way

We can use DBA_OBJECTS_AE to find all the triggers across all editions, and then for each one to “jump” to its edition using dbms_sql and check whether it is a crosseedition trigger or not using DBA_TRIGGERS. Continue reading “Pre-19c Crossedition Triggers”

USER_TRIGGERS_AE

One of the fundamental building blocks of EBR (Edition Based Redefinition) is Crossedition Triggers.

This special type of triggers is used when we change the structure of tables in an online application upgrade, and the data of these tables may be changed while we do the upgrade and afterwards, until no session uses the pre-upgrade edition. These triggers keep the pre-upgrade representation of the data and post-upgrade representation of the data in sync.

Crossedition triggers are temporary in nature – they should be dropped when all the clients use the edition in which the triggers were created (or one of its descendent editions).

In theory, crossedition triggers live for a very short period.

In real life, when we have multiple active editions and different types of clients that may keep using older editions for a while before upgrading to the latest edition, it may take days and even weeks before we can drop the crossedition triggers. In my opinion, this is perfectly fine, and actually one of the benefits that I see in EBR.

Another significant difference between crossedition triggers and regular triggers is that crossedition triggers are not inherited by descendent editions. However, they may be triggered by DML operations that are executed in a descendent edition.

This means that we need a way to check which crossedition triggers exist in our schema/database.
Continue reading “USER_TRIGGERS_AE”

Frequency-Based Ranges – Generalized with SQL Macro

In the previous post I used MATCH_RECOGNIZE to convert a list of concrete event times to frequency-based ranges.
The same technique can be applied with different tables, so I wrapped the query in a Table SQL Macro.

The parameters are the table, the “type” column, the “time” column, and (optionally) the period used for filtering.
I assumed the frequencies are always in minutes. The macro can be further generalized to support different time resolutions.

Here is the code:

 create or replace function freq_view ( i_table in dbms_tf.table_t, i_columns in dbms_tf.columns_t, i_from_time in date default date '1970-01-01', i_to_time in date default date '9999-12-31' ) return varchar2 sql_macro as begin return 'select event_type ' || i_columns(1) || ', to_char(from_time,''yyyy-mm-dd'') "DATE", to_char(from_time,''hh24:mi'') || nvl2(to_time, '' - '' || to_char(to_time,''hh24:mi'') || '' every '' || minutes || '' minutes ('' || number_of_events || '' events)'', null) event_times from ( select ' || i_columns(1) || ' as event_type, trunc(' || i_columns(2) || ',''mi'') as event_time from i_table where ' || i_columns(2) || ' between i_from_time and i_to_time ) match_recognize ( partition by event_type order by event_time measures X.event_time from_time, last(Y.event_time) to_time, round((first(Y.event_time)-X.event_time)*24*60) minutes, count(*) number_of_events pattern (X Y{2,} | X) define Y as event_time-prev(event_time) = first(Y.event_time)-X.event_time ) order by event_type, from_time'; end freq_view; /

Let’s use it for the same data set used in the original post:

 SQL> break on event_type skip 1 dup SQL> select * from freq_view(events,columns(event_type,event_time),timestamp'2022-07-29 08:00:00'); EVENT_ DATE EVENT_TIMES ------ -------------- ---------------------------------------------- A 2022-07-29 08:10 - 08:50 every 10 minutes (5 events) A 2022-07-29 08:58 - 09:22 every 8 minutes (4 events) A 2022-07-29 09:33 A 2022-07-29 09:42 - 10:52 every 10 minutes (8 events) A 2022-07-29 10:59 - 11:11 every 4 minutes (4 events) A 2022-07-29 11:21 - 12:01 every 10 minutes (5 events) B 2022-07-29 08:15 - 09:55 every 20 minutes (6 events) B 2022-07-29 10:10 B 2022-07-29 10:25 - 12:05 every 25 minutes (5 events) C 2022-07-29 08:02 - 08:32 every 2 minutes (16 events) C 2022-07-29 08:49 - 09:05 every 2 minutes (9 events) C 2022-07-29 09:08 - 09:20 every 4 minutes (4 events) C 2022-07-29 09:22 - 10:16 every 2 minutes (28 events) C 2022-07-29 11:46 - 12:20 every 2 minutes (18 events) 14 rows selected.

And now let’s use it for something else – the DBMS_SCHEDULER executions from the last 3 hours:

 SQL> select * from freq_view(user_scheduler_job_run_details,columns(job_name,log_date),sysdate-3/24); JOB_NAME DATE EVENT_TIMES ------------ --------------- ------------------------------------------------ JOB_AAA 2022-07-30 08:45 - 10:45 every 60 minutes (3 events) JOB_BBB 2022-07-30 08:10 - 10:10 every 60 minutes (3 events) JOB_CCC 2022-07-30 08:07 - 11:02 every 5 minutes (36 events) JOB_DDD 2022-07-30 10:00 JOB_EEE 2022-07-30 08:06 - 11:05 every 1 minutes (180 events)

Pattern Matching and Event Frequencies

I have a table that contains events of various types:

 create table events ( event_id integer not null, event_type varchar2(2) not null, event_time date not null, -- some other columns with the event details );

The events may arrive sporadically, but many times multiple consecutive events of the same type arrive in a fixed frequency (e.g., “events of type A happen every 10 minutes between 8:10am and 8:50am”).

I want to list all the event times in a specific period for every event type.

This is a simple query, but there are many results, so it’s not very easy to read them:

 SQL> break on event_type skip 1 dup SQL> select event_type, 2 event_time 3 from events 4 where event_time > timestamp'2022-07-29 08:00:00' 5 order by event_type, 6 event_time; EVENT TYPE EVENT_TIME ----- ------------------- A 2022-07-29 08:10:00 A 2022-07-29 08:20:00 A 2022-07-29 08:30:00 A 2022-07-29 08:40:00 A 2022-07-29 08:50:00 A 2022-07-29 08:58:00 A 2022-07-29 09:06:00 A 2022-07-29 09:14:00 A 2022-07-29 09:22:00 A 2022-07-29 09:33:00 A 2022-07-29 09:42:00 A 2022-07-29 09:52:00 A 2022-07-29 10:02:00 A 2022-07-29 10:12:00 A 2022-07-29 10:22:00 A 2022-07-29 10:32:00 A 2022-07-29 10:42:00 A 2022-07-29 10:52:00 A 2022-07-29 10:59:00 A 2022-07-29 11:03:00 A 2022-07-29 11:07:00 A 2022-07-29 11:11:00 A 2022-07-29 11:21:00 A 2022-07-29 11:31:00 A 2022-07-29 11:41:00 A 2022-07-29 11:51:00 A 2022-07-29 12:01:00 B 2022-07-29 08:15:00 B 2022-07-29 08:35:00 B 2022-07-29 08:55:00 B 2022-07-29 09:15:00 B 2022-07-29 09:35:00 B 2022-07-29 09:55:00 B 2022-07-29 10:10:00 B 2022-07-29 10:25:00 B 2022-07-29 10:50:00 B 2022-07-29 11:15:00 B 2022-07-29 11:40:00 B 2022-07-29 12:05:00 C 2022-07-29 08:02:00 C 2022-07-29 08:04:00 C 2022-07-29 08:06:00 C 2022-07-29 08:08:00 C 2022-07-29 08:10:00 C 2022-07-29 08:12:00 C 2022-07-29 08:14:00 C 2022-07-29 08:16:00 C 2022-07-29 08:18:00 C 2022-07-29 08:20:00 C 2022-07-29 08:22:00 C 2022-07-29 08:24:00 C 2022-07-29 08:26:00 C 2022-07-29 08:28:00 C 2022-07-29 08:30:00 C 2022-07-29 08:32:00 C 2022-07-29 08:49:00 C 2022-07-29 08:51:00 ... 114 rows selected.

Because this list contains batches of events with a fixed frequency, we can change the query to get a nicer output – with a single line for every batch.
Identifying these batches is easy with the MATCH_RECOGNIZE clause.

Let’s see first the output of the query:

 EVENT TYPE DATE EVENT_TIMES ----- ------------ ------------------------------------------- A 2022-07-29 08:10 - 08:50 every 10 minutes (5 events) A 2022-07-29 08:58 - 09:22 every 8 minutes (4 events) A 2022-07-29 09:33 A 2022-07-29 09:42 - 10:52 every 10 minutes (8 events) A 2022-07-29 10:59 - 11:11 every 4 minutes (4 events) A 2022-07-29 11:21 - 12:01 every 10 minutes (5 events) B 2022-07-29 08:15 - 09:55 every 20 minutes (6 events) B 2022-07-29 10:10 B 2022-07-29 10:25 - 12:05 every 25 minutes (5 events) C 2022-07-29 08:02 - 08:32 every 2 minutes (16 events) C 2022-07-29 08:49 - 09:05 every 2 minutes (9 events) C 2022-07-29 09:08 - 09:20 every 4 minutes (4 events) C 2022-07-29 09:22 - 10:16 every 2 minutes (28 events) C 2022-07-29 11:46 - 12:20 every 2 minutes (18 events) 14 rows selected.

Much nicer, isn’t it? 🙂

Here is the query:

 SQL> select event_type, 2 to_char(from_time,'yyyy-mm-dd') "DATE", 3 to_char(from_time,'hh24:mi') || 4 nvl2(to_time, 5 ' - ' || to_char(to_time,'hh24:mi') 6 || ' every ' || minutes || ' minutes (' 7 || number_of_events || ' events)', 8 null) event_times 9 from ( 10 select event_type,event_time 11 from events 12 where event_time > timestamp'2022-07-29 08:00:00' 13 ) match_recognize ( 14 partition by event_type 15 order by event_time 16 measures X.event_time from_time, 17 last(Y.event_time) to_time, 18 (first(Y.event_time)-X.event_time)*24*60 minutes, 19 count(*) number_of_events 20 pattern (X Y{2,} | X) 21 define 22 Y as event_time-prev(event_time) = first(Y.event_time)-X.event_time 23 ) 24 order by event_type, from_time;

The original query [lines 10-12] is the input to the MATCH_RECOGNIZE clause [lines 13-23].

We partition the input record-set by EVENT_TYPE [line 14], as we want to get a separate list for each type.

MATCH_RECOGNIZE is used for finding consecutive rows that match some pattern. “Consecutive” means we have to define the order of the input record-set (in each partition). In this case, we order the events of each type by EVENT_TIME [line 15].

Now, let’s define the pattern [lines 20-22]. We’re looking for at least 3 consecutive events – an X record followed by two or more Y records [“X Y{2,}”].
Any event is potentially an X record, so I didn’t define it explicitly.
Y is explicitly defined [line 22] as a record that its time difference from the previous record [event_time-prev(event_time)] is the same as the time difference between the first event [X] and the second event [the first Y] in the current batch [first(Y.event_time)-X.event_time].
If we don’t find such a batch of events [X followed by two or more Ys], we have a single event to return – X. This is expressed by the pattern “X Y{2,} | X” [line 20].

For every match of this pattern, 4 expressions are calculated [lines 16-19]:
FROM_TIME: the time of the first event in the match [line 16]
TO_TIME: the time of the last event in the match [line 17]
MINUTES: the time difference between every two consecutive events [line 18]
NUMBER_OF_EVENTS: the total number of events in the match [line 19]

Finally, we display the results returned by the MATCH_RECOGNIZE operator, using the expressions calculated in the MEASURES clause [lines 1-8].

The same technique can be applied for different tables. See the next post for a generalized solution, using a Table SQL Macro.

FORALL – INDICES OF vs. Lower and Upper Bounds

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 🙂