"Create a table with increasing contiguous numbers in
col1, and updateSET col1 = col1 + 1. It temporarily violates the unique constraint until the end of the statement. How do unique indexes allow that?"— Inspired by a question from Frank Pachot (on my LinkedIn Post)
Introduction
Oracle's Unique Indexes are generally perceived as simple:
A duplicate key is not allowed.
But what happens when every key changes simultaneously?
Consider the following table:
1
2
3
4
5
6
7
8
9
10
Now execute:
UPDATE t_uniq_upd SET col1 = col1 + 1;
If Oracle updates row-by-row, shouldn't the very first update create two rows with value 2?
1 -> 2
Existing 2 already exists
Shouldn't Oracle immediately throw
ORA-00001: unique constraint violated
Yet...
It doesn't. The statement succeeds.
That single question led me into one of the most interesting Oracle internals investigations I've done in recent times.
Instead of accepting assumptions, I decided to reproduce the behavior from scratch, trace Oracle's execution, analyze TKPROF output, inspect Error Stack dumps and understand how Oracle actually maintains uniqueness during UPDATE operations.
Objectives
This investigation attempts to answer:
Why does a full table UPDATE succeed?
Why does a partial UPDATE fail?
When is uniqueness actually checked?
Is uniqueness verified row-by-row?
Does Oracle update everything first and rollback later?
What role does the Unique Index play?
Can trace files reveal Oracle's internal execution path?
Lab Environment
Oracle Database 19c
Fresh user created for every execution.
Before every test:
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
This ensured every execution started from a clean environment.
Test Setup
DROP USER ind_tst CASCADE;
CREATE USER ind_tst IDENTIFIED BY ind_tst;
GRANT DBA TO ind_tst;
Connect as IND_TST
CREATE TABLE t_uniq_upd(col1 NUMBER);
INSERT INTO t_uniq_upd
SELECT LEVEL FROM dual CONNECT BY LEVEL <=10;
COMMIT;
CREATE UNIQUE INDEX uix_t_uniq_upd ON t_uniq_upd(col1);
EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname=>'IND_TST',
tabname=>'T_UNIQ_UPD',
cascade=>TRUE);
Initial data
1
2
3
4
5
6
7
8
9
10
Trace Configuration
To observe Oracle internals:
ALTER SESSION SET TRACEFILE_IDENTIFIER='UNIQUE_ERROR_STACK';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
ALTER SESSION SET EVENTS '1 trace name errorstack level 1';
ALTER SESSION SET EVENTS '1 trace name errorstack off';
ALTER SESSION SET EVENTS '10046 trace name context off';
Test Case 1
Partial Update (Expected Failure)
UPDATE t_uniq_upd SET col1=col1+1 WHERE col1 BETWEEN 1 AND 5;
Result
ORA-00001 unique constraint violated
Why?
Oracle attempts to produce
2 3 4 5 66 <-7 8 9 10
The row containing value 6 is outside the UPDATE set. Therefore
5 -> 6
conflicts with an existing row that remains
6 -> 6
This is a genuine duplicate.
Test Case 2
Partial Update (Successful)
UPDATE t_uniq_upd SET col1=col1+1 WHERE col1 BETWEEN 5 AND 10;
Result
6
7
8
9
10
11
No error. Why?
Because every conflicting key is also moving.
10 -> 11
9 -> 10
8 -> 9
...
5 -> 6
No unaffected row already owns those destination keys.
Test Case 3
Full Table Update
UPDATE t_uniq_upd SET col1=col1+1;
Result
2
3
4
5
6
7
8
9
10
11
No ORA-00001.
This is the question that started the investigation.
Initial Assumption
Many Oracle professionals initially assume Oracle simply updates rows in reverse order. That explanation seems reasonable.
Unfortunately...
It doesn't explain every scenario. Changing ranges quickly exposes cases where this theory is insufficient.
It became clear that Oracle was doing something much smarter internally.
Digging into Oracle Internals
To understand Oracle's behaviour I collected:
Level 12 10046 trace
TKPROF
Error Stack
Multiple executions
Cold cache runs
Shared pool flushes
Buffer cache flushes
Each execution was compared independently.
TKPROF Observations
The failing UPDATE produced output similar to:
INDEX RANGE SCAN
Rows = 5
Execute
Current = 19
Rows = 0
Interesting observations:
Oracle successfully found five candidate rows.
Oracle entered DML execution.
Oracle acquired current-mode blocks.
Yet the statement completed with zero updated rows.
The successful UPDATE showed
Current = 22
Rows = 6
meaning Oracle completed DML successfully.
Error Stack
The Error Stack showed Oracle raising ORA-00001 during UPDATE execution.
The stack contained UPDATE execution functions including
|
`-- updexe
|
`-- updThreePhaseExe
|
`-- updaul
|
`-- qerstFetch
|
`-- qerupFetch
|
`-- kxibFinish
|
`-- kxibDoFinish
|
`-- kauXuFinish
|
`-- kauerr
|
`-- ORA-00001
This was an important observation. The duplicate wasn't discovered during parsing.
Nor during COMMIT.
It occurred while Oracle was executing the UPDATE.
Recursive SQL
Initially, I believed Oracle might be issuing recursive SQL to search the Unique Index for duplicate keys.
Tracing proved otherwise.
The recursive SQL was primarily used to resolve metadata required to produce the final error message:
ORA-00001: unique constraint (IND_TST.UIX_T_UNIQ_UPD) violated
Oracle already knew the object number internally and queried the data dictionary to convert it into a readable owner and index/constraint name.
Unique Index vs Non-Unique Index
This investigation also reinforced an important concept that is often misunderstood.
Unique Index
A unique index stores only the indexed key because each key value is guaranteed to be unique.
Conceptually:
10
11
12
13
There is only one possible ROWID for each key.
Oracle can therefore identify the row directly from the index entry.
Non-Unique Index
A non-unique index must distinguish rows having the same indexed value.
Oracle achieves physical uniqueness by including the ROWID in the index entry.
Conceptually:
10 + ROWID A
10 + ROWID B
10 + ROWID C
Although the logical key is duplicated, the combination:
(key, ROWID)
is unique inside the B-tree.
This is why Oracle can efficiently store many identical key values in a non-unique index while still locating each row unambiguously.
Note: A unique constraint is enforced using a unique index (or an equivalent internal index structure). A non-unique index by itself does not enforce uniqueness, even though its entries are physically unique because of the appended ROWID.
Another Interesting Observation
The execution plan for every test remained:
INDEX RANGE SCAN
Many developers associate Range Scans only with non-unique indexes.
In reality, Oracle can perform an INDEX RANGE SCAN on a unique index whenever the predicate matches a range of values, such as:
WHERE col1 BETWEEN 1 AND 5
Oracle is not looking for one key.
It is scanning a contiguous section of the B-tree.
The Final Answer
After all the tracing, testing and analysis, the biggest takeaway was surprisingly simple.
Oracle does not check uniqueness row-by-row in the physical update order.When every affected key participates in the same UPDATE statement, Oracle allows those keys to move together as part of the statement without treating intermediate states as violations.
However, if a destination key belongs to a row outside the UPDATE set, Oracle detects a real conflict during unique index maintenance, raises ORA-00001, and automatically rolls back the entire statement to its implicit statement-level savepoint.
That explains why:
UPDATE t_uniq_upd SET col1=col1+1;
succeeds, while
UPDATE t_uniq_upd SET col1=col1+1 WHERE col1 BETWEEN 1 AND 5;
fails.
Key Takeaways
✓ Oracle can perform an INDEX RANGE SCAN on a unique index.
✓ Unique indexes and non-unique indexes have different entry formats.
✓ Non-unique indexes achieve physical uniqueness using ROWID.
✓ ORA-00001 is raised during UPDATE execution, not during parsing or COMMIT.
✓ Current-mode gets prove Oracle entered the DML execution path.
✓ Error Stack shows the exception originates from the UPDATE execution path.
✓ Recursive SQL observed in the trace is primarily related to dictionary metadata required to format the error message.
✓ The most important lesson:
Oracle does not enforce uniqueness by simply checking rows in physical update order.
Final Thoughts
One deceptively simple question led to several hours of tracing, experimentation and validation.
That's what makes Oracle internals so fascinating.
The answer isn't always hidden in the documentation, it often reveals itself only after building a reproducible test case, collecting evidence and letting the database tell its own story.
Sometimes, the journey to the answer teaches more than the answer itself.