Following ROWIDs Through an Oracle Unique Index Update

Initial SQL Example

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

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 }

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:

• The initial index entries are: (-1): row #1 and (1): row #2
• Updating the first row deletes the first entry (-1): row #1 and adds one with (1): row #1
• Updating the second row deletes the corresponding entry (1): row #2 and adds one with (-1): row #2
• 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

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

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';

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"

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

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:

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;

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

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

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

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

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

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.

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

The final state is:

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 1000');