DEV Community

Cover image for Following ROWIDs Through an Oracle Unique Index Update
Franck Pachot
Franck Pachot

Posted on

Following ROWIDs Through an Oracle Unique Index Update

#oracle #postgres #sql #index

I've always been amazed by how Oracle Database handles updates to a unique column—performing set-based operations that don't violate the unique constraint, yet when executed row by row, it temporarily permits duplicates.

SQL> create table franck ( val int unique );

Table created.

SQL> insert into franck values (-1) , (1) ;

2 rows created.

SQL> select val from franck;

       VAL
----------
        -1
         1

SQL> update franck set val=-val;

2 rows updated.

SQL> select val from franck;

       VAL
----------
         1
        -1
Enter fullscreen mode Exit fullscreen mode

From a SQL perspective, this is expected behavior, but not all databases support it without raising an error:

  • Db2, SQL Server, and Oracle handle it without error.
  • PostgreSQL raises ERROR: duplicate key value violates unique constraint "franck_val_key", DETAIL: Key (val)=(1) already exists. This works with a deferred constraint.
  • MySQL or MariaDB raise Duplicate entry '1' for key 'franck.val'
  • SQLite raises { "code": "SQLITE_CONSTRAINT_UNIQUE" }
  • MongoDB raises E11000 duplicate key error collection: test.franck index: val_1 dup key: { val: 1 } 
db.franck.createIndex({ val: 1 }, { unique: true });
db.franck.insertMany([ { val: -1 }, { val: 1 } ]);
db.franck.updateMany({},[ { $set: { val: {$multiply:[ "$val",-1 ]} } } ]);
MongoServerError: Plan executor error during update :: caused by :: E11000 duplicate key error collection: test.franck index: val_1 dup key: { val: 1 }
Enter fullscreen mode Exit fullscreen mode

This is surprising because Oracle unique indexes store the indexed columns as the B-tree key and the ROWID as the associated data. Non-unique indexes add the ROWID to the physical key and are required for a deferrable unique constraint to allow temporary duplication before the end of the transaction. So how do non-deferrable unique indexes allow duplication during a single update statement? In this simple example, I would expect:

  1. The initial index entries are: (-1): row #1 and (1): row #2
  2. Updating the first row deletes the first entry (-1): row #1 and adds one with (1): row #1
  3. Updating the second row deletes the corresponding entry (1): row #2 and adds one with (-1): row #2
  4. The final non-deleted index entries are: (-1): row #2 and (1): row #1

Even if the final state is valid, this execution would raise a duplicate key error at step 2 because two entries have the same value: (1): row #1 and (1): row #2. So Oracle does something smarter.

Let's look at the internals with another example: five rows with values from 1 to 5, and an update that increments them by one should create a temporary violation until the last row is updated.

Investigation with block dumps

My goal is to observe how a unique B-tree index evolves as Oracle executes a multi-row update that temporarily creates duplicate values.

The table contains five rows with values from 1 to 5, and a row-level trigger pauses for five seconds before each row update. This gives me enough time to dump the index leaf block and capture intermediate states in a second session.

I create the table:

drop table FRANCK purge;
create table FRANCK as select rownum as VAL from xmltable('1 to 5');
create unique index FRANCK on FRANCK ( VAL );
create or replace trigger FRANCK_SLEEP 
 before update on FRANCK 
 for each row begin dbms_session.sleep(5); end;
/
show errors
Enter fullscreen mode Exit fullscreen mode

Here are my table's rows in the order they are scanned:

SQL> column "DUMP(VAL,16)" format a18
SQL> column "DUMP(ROWID,16)" format a39
SQL> select val, dump(val,16), dump(rowid,16)
     from FRANCK 
;

       VAL DUMP(VAL,16)       DUMP(ROWID,16)
---------- ------------------ ---------------------------------------
         1 Typ=2 Len=2: c1,2  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,0
         2 Typ=2 Len=2: c1,3  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,1
         3 Typ=2 Len=2: c1,4  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,2
         4 Typ=2 Len=2: c1,5  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,3
         5 Typ=2 Len=2: c1,6  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,4
Enter fullscreen mode Exit fullscreen mode

I've displayed the value in hexadecimal, as well as the ROWID because the index entries are key -> ROWID and I'll inspect them with a hexadecimal dump.

I locate the index block and my session's trace file:

alter session set tracefile_identifier = 'franck';

column fileno    new_value fileno
column block     new_value block
column tracefile new_value tracefile

select header_file as fileno ,
       header_block+1 as block
   from dba_segments
    where segment_type='INDEX'
      and segment_name='FRANCK'
      and owner=sys_context('USERENV', 'CURRENT_SCHEMA')
;

select value as tracefile
 from v$diag_info
 where name = 'Default Trace File'
;
Enter fullscreen mode Exit fullscreen mode

Each time I want to dump the block and read the entries, I run the following in the session where the variables were defined:

alter system checkpoint;
alter system dump datafile &fileno block █
host tail -20 &tracefile | egrep "^row|^col"
Enter fullscreen mode Exit fullscreen mode

I'll dump the block multiple times while another session runs update FRANCK set VAL = VAL + 1;, showing:

  • the index entry (row# in the dump)
  • with the 6-byte ROWID (data in the dump)
  • and the key (col 0 in the dump).

Initial state

The index leaf block before the update contains one entry per table row:

row#0[8021] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 06
Enter fullscreen mode Exit fullscreen mode

The six-byte data:(6) field is the ROWID stored in the unique index entry. The indexed value is stored in col 0 as it is a single-column index.

To help with the hexadecimal values, I've put them in a table to show the table rows and their index entries, with values in hexadecimal and decimal:

table value table rowid index entry index key index data
1 = c1 02 69 00 00 #0 c1 02 = 1 69 00 00
2 = c1 03 69 00 01 #1 c1 03 = 2 69 00 01
3 = c1 04 69 00 02 #2 c1 04 = 3 69 00 02
4 = c1 05 69 00 03 #3 c1 05 = 4 69 00 03
5 = c1 06 69 00 04 #4 c1 06 = 5 69 00 04

Running the update

I run the update in another session, and thanks to the trigger, it waits 5 seconds between each updated row:


update FRANCK set val = val + 1
;
Enter fullscreen mode Exit fullscreen mode

The update starts the scan and processes the first table row, where the value 1 (c1 02) is incremented to 2 (c1 03).

Oracle first locates the index entry for the old value, acquires an exclusive lock on the row (the lock: 2 references ITL slot 2, whose transaction information identifies the modifying transaction), and marks it as deleted (-D- flag):

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 06
Enter fullscreen mode Exit fullscreen mode

At this intermediate point, the old entry has been deleted, and no entry references the row containing the new value:

table value table rowid index entry index key index data
2 = c1 03 69 00 00 #0 deleted c1 02 = 1 69 00 00
2 = c1 03 69 00 01 #1 c1 03 = 2 69 00 01
3 = c1 04 69 00 02 #2 c1 04 = 3 69 00 02
4 = c1 05 69 00 03 #3 c1 05 = 4 69 00 03
5 = c1 06 69 00 04 #4 c1 06 = 5 69 00 04

The next dump is interesting. Oracle does not add another entry for value 2 (c1 03). Instead, it updates the existing entry #1, which already has the right key c1 03 but for a different row. The old ROWID of entry #1 is replaced:

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 06
Enter fullscreen mode Exit fullscreen mode

There are now two index entries for rowid 69 00 00, one deleted and one current:

table value table rowid index entry index key index data
2 = c1 03 69 00 00 #0 deleted c1 02 = 1 69 00 00
2 = c1 03 69 00 00 #1 c1 03 = 2 69 00 00
3 = c1 04 69 00 02 #2 c1 04 = 3 69 00 02
4 = c1 05 69 00 03 #3 c1 05 = 4 69 00 03
5 = c1 06 69 00 04 #4 c1 06 = 5 69 00 04

Notice that there is no longer any entry for rowid 69 00 01 because Oracle has changed the ROWID stored in the existing key for value 2 (c1 03) entry from 69 00 01 to 69 00 00. This is why there's no duplicate key situation: all keys in the index are unique.

If the update stops after modifying only the first row, it can create duplicate key issues because the first two rows share the same value. It can also cause logical corruption since the second row lacks an index entry. One possible approach is for Oracle to track these uniqueness conflicts as violation counts during statement processing and confirm that none persist at the end. This strategy would enable Oracle to proceed, expecting that further updates will restore consistency.

The update then continues with the next table row. Rowid 69 00 01 changes from 2 to 3.

I guess that Oracle attempts to locate the entry with key value 2 (c1 03) pointing to 69 00 01 for deletion. However, it finds the key but associated with a different ROWID. Since there's nothing to delete, it leaves it as-is but decreases the violation counter, which then returns to zero, because the current update removes the value that was a duplicate.

As we have seen before, Oracle updates the index entry for the new value c1 04 = 3 to point to rowid 69 00 01:

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 06
Enter fullscreen mode Exit fullscreen mode

Currently, there is an index entry for 69 00 01 with the value 3 (c1 04), but no entry exists for row 69 00 02, so continuing with my guess, the violation counter is incremented.

table value table rowid index entry index key index data
2 = c1 03 69 00 00 #0 deleted c1 02 = 1 69 00 00
2 = c1 03 69 00 00 #1 c1 03 = 2 69 00 00
3 = c1 04 69 00 01 #2 c1 04 = 3 69 00 01
4 = c1 05 69 00 03 #3 c1 05 = 4 69 00 03
5 = c1 06 69 00 04 #4 c1 06 = 5 69 00 04

With this pattern, the ROWID associated with each key shifts one row earlier. The same pattern applies to keys 4 and 5. The violation counter may be decremented and then incremented again, remaining at 1.

After processing the row containing the value 4 (c1 05), the key with value 5 (c1 06) points to rowid 69 00 03. The violation counter remains at 1.

Once the row with value 5 (c1 06) is processed, Oracle encounters a situation where no existing key is reused. According to my guess, the violation counter decreases because the previous value no longer matches the current row. The new key value is 6 (c1 07), which did not previously exist. Only then is a new index entry created:

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 06

row#5[7966] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 07
Enter fullscreen mode Exit fullscreen mode

The final state is:

table value table rowid index entry index key index data
2 = c1 03 69 00 00 #0 deleted c1 02 = 1 69 00 00
2 = c1 03 69 00 00 #1 c1 03 = 2 69 00 00
3 = c1 04 69 00 01 #2 c1 04 = 3 69 00 01
4 = c1 05 69 00 02 #3 c1 05 = 4 69 00 02
5 = c1 06 69 00 03 #4 c1 06 = 5 69 00 03
6 = c1 07 69 00 04 #5 c1 07 = 6 69 00 04

All rows and index entries are consistent, and the violation counter is back to zero. The index structure I examined never had duplicate keys in the block dumps. Oracle maintains key uniqueness by reusing entries and updating their stored ROWIDs. The method Oracle uses to monitor unresolved uniqueness conflicts until the statement finishes isn't clear from these dumps, but a violation counter appears to be a possible implementation.

UNIQUE vs non-UNIQUE index

Without grasping these internals, one might assume a UNIQUE index isn't necessary for a UNIQUE constraint. However, now we see the performance benefit of this update pattern: it modifies existing index entries rather than deleting the old value and inserting the new one. I tested this with one million rows:

set linesize 200 pagesize 1000
drop table FRANCK purge;
create table FRANCK as select rownum as VAL from xmltable('1 to 1000000');
create unique index FRANCK on FRANCK ( VAL );
update FRANCK set val = val + 1 ;
analyze index FRANCK validate structure;
select height, br_rows, lf_rows, del_lf_rows, br_blks, lf_blks
 from index_stats
;
Enter fullscreen mode Exit fullscreen mode

The index statistics display branch and leaf blocks along with entries. Following the update statement that affected all rows, the unique index only added a single deleted leaf entry (DEL_LF_ROWS):


    HEIGHT    BR_ROWS    LF_ROWS DEL_LF_ROWS    BR_BLKS    LF_BLKS
---------- ---------- ---------- ----------- ---------- ----------
         3       2220    1000001           1          5       2221
Enter fullscreen mode Exit fullscreen mode

I tested the same process using a non-unique index:

set linesize 200 pagesize 1000
drop table FRANCK purge;
create table FRANCK as select rownum as VAL from xmltable('1 to 1000000');
create index FRANCK on FRANCK ( VAL );
update FRANCK set val = val + 1 ;
analyze index FRANCK validate structure;
select height, br_rows, lf_rows, del_lf_rows, br_blks, lf_blks
 from index_stats
;
Enter fullscreen mode Exit fullscreen mode

Using the non-unique index allows temporary violations until the end of the transaction, if the constraint is deferred, but nearly every updated row in this test left a deleted entry, resulting in the number of leaf entries and leaf blocks being nearly twice as many as with the unique index:


    HEIGHT    BR_ROWS    LF_ROWS DEL_LF_ROWS    BR_BLKS    LF_BLKS
---------- ---------- ---------- ----------- ---------- ----------
         3       4830    1999530      999530         13       4831
Enter fullscreen mode Exit fullscreen mode

In Oracle, a unique constraint does not always need a unique index. However, in this specific update pattern, the unique index involves significantly fewer index modifications and uses fewer leaf blocks compared to a non-unique index doing the same job.

Conclusion

The block dumps illustrate the physical behavior: during processing, Oracle reuses existing unique-key entries by updating their stored ROWIDs. When each value shifts to the next key, the associated ROWID also moves to the preceding row in the table. Only the last value, 6, necessitates a new index entry.

The dumps do not disclose the precise statement-level mechanism Oracle employs to differentiate a temporary collision—resolved later in the statement—from a genuine uniqueness violation. A conflict counter is one potential implementation, but this remains speculative. Confirming it would require more tracing or evidence beyond leaf-block states. To date, no public documentation confirms this implementation. In contrast, SQL Server exposes how it handles this issue, as explained by Craig Freedman in Maintaining Unique Indexes: the execution plan reveals a complex reorganization of the update order through Split, Filter, and Sort Collapse operators.

The behavior we observed on Oracle Database appears to be ideal for set-based updates, and Oracle's implementation seems relatively straightforward. Why do other databases like PostgreSQL differ? And why does SQL Server require more complex processing? I can suggest two reasons:

  • PostgreSQL allows duplicate keys in indexes because of its MVCC approach with heap-based visibility. It must retain deleted entries as long as the dead tuples are visible to ensure previous snapshot queries find the rows. Oracle uses block-level MVCC, so even if old entries aren't in the current block, they reappear when undo information is used to create a consistent read snapshot.
  • PostgreSQL displays the specific row causing a constraint violation, such as DETAIL: Key (val)=(2) already exists., immediately after the first update in my example. Oracle, however, defers error reporting until the statement ends, which can result in loss of this detailed info at error time (see oracle vs. postgres).

Typically, no single database is universally better. Each involves different trade-offs. One database enables efficient bulk updates, allowing temporary constraint violations without deferring constraints, while another specifies the exact row causing a constraint violation. SQL Server appears to blend these approaches: it avoids raising transient violations and indicates the offending value when a violation happens, at the cost of reordering the rows being updated.

Top comments (0)