ORA-00001.5: unique constraint NOT violated

Recently, I came across a very interesting “feature” in Oracle – prescience. Yes indeed, this newly discovered and bizarre behavior of Oracle borders on precognition. Just when I thought Oracle only knew how to operate in the now, and how to deal with the past, it can tell the future! Okay already, enough with the melodrama and on to the experience.

I was testing a particular multi-row update scenario in an attempt to discern Oracle’s approach to managing the redo. I used LogMiner and a log file dump to help understand this mechanic. The test involved a heap table with two columns and a primary key enforced using a unique index. The table was loaded with ten rows.

eric@erice:SQL> desc parent_table_1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
COL1                                      NOT NULL NUMBER
COL2                                               NUMBER

eric@erice:SQL> select * from parent_table_1;

      COL1       COL2
---------- ----------
         0          1
         1          1
         2          1
         3          1
         4          1
         5          1
         6          1
         7          1
         8          1
         9          1

10 rows selected.

eric@erice:SQL> select index_name from user_indexes where table_name = 'PARENT_TABLE_1';

INDEX_NAME
------------------------------
PK_PARENT_TABLE_1

eric@erice:SQL> select constraint_name, constraint_type, deferrable, validated, rely, index_name
from user_constraints where table_name = 'PARENT_TABLE_1';


CONSTRAINT_NAME                C DEFERRABLE     VALIDATED     RELY INDEX_NAME
------------------------------ - -------------- ------------- ---- ----------------------
PK_PARENT_TABLE_1              P NOT DEFERRABLE VALIDATED          PK_PARENT_TABLE_1

I wanted to understand the redo mechanics as it related to constraint validation for a multi-row update against PARENT_TABLE_1 using the following simple update statement:

eric@erice:SQL> update parent_table_1 set col1=col1+1;

10 rows updated.

eric@erice:SQL> commit;

Commit complete.

eric@erice:SQL> select * from parent_table_1;

      COL1       COL2
---------- ----------
         1          1
         2          1
         3          1
         4          1
         5          1
         6          1
         7          1
         8          1
         9          1
        10          1

10 rows selected.

eric@erice:SQL> select * from table(dbms_xplan.display());

------------------------------------------------------------------------
 Id   Operation              Name            Rows   Bytes  Cost
------------------------------------------------------------------------
   0  UPDATE STATEMENT
   1   UPDATE               PARENT_TABLE_1
   2    TABLE ACCESS FULL   PARENT_TABLE_1
------------------------------------------------------------------------

I must admit, when I saw all rows updated sans unique constraint violation I was taken aback. This statement should have failed after the first row was processed, right? I ran the test again with the same results. I then interrogated the data dictionary looking for some database condition that might rationally explain this behavior. I was left staring at the screen having yielded my intuition to what must be a quirk, some kind of bug or my ignorance. How did Oracle know that my statement would eventually yield a set of unique keys? Not all was lost. Oracle couldn’t hide the redo!

Log file dumps are not pretty and take a couple “reads” before you get a handle on what is happening. To display all the applicable dump here would consume more space than desired. Thankfully, LogMiner can format much of the information for us.

eric@erice:SQL> select scn,rel_file#,data_blk#,data_obj#,substr(operation,1,20) OP from v$logmnr_contents;

            SCN  REL_FILE#  DATA_BLK#  DATA_OBJ# OP
--------------- ---------- ---------- ---------- --------------------
        4571216          0          0          0 START
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          0          0       7149 INTERNAL

eric@erice:SQL> select object_name, object_id from user_objects where object_id in ('6480','7149');

OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
PK_PARENT_TABLE_1                    7149
PARENT_TABLE_1                       6480

From the LogMiner data we can see that Oracle performs alternating modifications on PARENT_TABLE_1 and PK_PARENT_TABLE_1 with two consecutive index modifications at the end. I took the liberty to “scrub” the log file dump using a Solaris egrep command. Annotations were added for the first few entries and last few entries to depict the pattern.

> egrep '^colindex redokeydatakey :' log_file.dmp
col  0: [ 1]  80    (table undo) 80 == 0 in the log file dump.
col  0: [ 2]  c1 02    (table redo) change 0 -> 1
key :(2):  01 80        (index undo) Index key value of 0.
keydata/bitmap: (6):  00 c0 02 2a 00 00   (index undo) old rowid data for key 0.
index redo (kdxlde):  delete leaf row    (index redo)
col  0: [ 2]  c1 02    (table undo) old value is 1
col  0: [ 2]  c1 03    (table redo) new value is 2
update keydata in row    (index undo)
key :(3):  02 c1 02    (index undo) Index key value of 1.
keydata/bitmap: (6):  00 c0 02 2a 00 01  (index undo) rowed data for key 1.
index redo (kdxlpu): update keydata, count=3 (index redo) update keydata operation
keydata : (6):  00 c0 02 2a 00 00  (index redo) new rowid data for key 1.
col  0: [ 2]  c1 03    (table undo) old value is 2.
col  0: [ 2]  c1 04    (table redo) new value is 3.
update keydata in row    (index undo)
key :(3):  02 c1 03    (index undo) Index key value of 2.
keydata/bitmap: (6):  00 c0 02 2a 00 02  (index undo) rowid data for key 2.
index redo (kdxlpu): update keydata, count=3 (index redo) update keydata operation
keydata : (6):  00 c0 02 2a 00 01  (index redo) new rowid data for key 2.
col  0: [ 2]  c1 04    (table undo) old value is 3.
col  0: [ 2]  c1 05    (table redo) new value is 4.
update keydata in row
key :(3):  02 c1 04
keydata/bitmap: (6):  00 c0 02 2a 00 03
index redo (kdxlpu): update keydata, count=3
keydata : (6):  00 c0 02 2a 00 02
col  0: [ 2]  c1 05
col  0: [ 2]  c1 06
update keydata in row
key :(3):  02 c1 05
keydata/bitmap: (6):  00 c0 02 2a 00 04
index redo (kdxlpu): update keydata, count=3
keydata : (6):  00 c0 02 2a 00 03
col  0: [ 2]  c1 06
col  0: [ 2]  c1 07
update keydata in row
key :(3):  02 c1 06
keydata/bitmap: (6):  00 c0 02 2a 00 05
index redo (kdxlpu): update keydata, count=3
keydata : (6):  00 c0 02 2a 00 04
col  0: [ 2]  c1 07
col  0: [ 2]  c1 08
update keydata in row
key :(3):  02 c1 07
keydata/bitmap: (6):  00 c0 02 2a 00 06
index redo (kdxlpu): update keydata, count=3
keydata : (6):  00 c0 02 2a 00 05
col  0: [ 2]  c1 08
col  0: [ 2]  c1 09
update keydata in row
key :(3):  02 c1 08
keydata/bitmap: (6):  00 c0 02 2a 00 07
index redo (kdxlpu): update keydata, count=3
keydata : (6):  00 c0 02 2a 00 06
col  0: [ 2]  c1 09
col  0: [ 2]  c1 0a
update keydata in row
key :(3):  02 c1 09
keydata/bitmap: (6):  00 c0 02 2a 00 08
index redo (kdxlpu): update keydata, count=3
keydata : (6):  00 c0 02 2a 00 07
col  0: [ 2]  c1 0a    (table undo) old value is 9.
col  0: [ 2]  c1 0b    (table redo) new value is 10.
update keydata in row    (index undo)
key :(3):  02 c1 0a    (index undo) Index key value of 9.
keydata/bitmap: (6):  00 c0 02 2a 00 13  (index undo) rowid data for key 9.
index redo (kdxlpu): update keydata, count=3 (index redo) update keydata operation.
keydata : (6):  00 c0 02 2a 00 08  (index redo) new rowid data for key 9.
key :(3):  02 c1 0b    (index undo) Index key value of 10.
index redo (kdxlin):  insert leaf row  (index redo)
keydata: (6):  00 c0 02 2a 00 13  (index redo) new rowid data for key 10.

As you can see, Oracle has taken the following approach in managing the index while performing this update.

1. Update table data (col0) for row 1 from 0 to 1.
2. Delete leaf row for index entry with key value of 0.
3. Update table data (col0) for row 2 from 1 to 2.
4. Update the key data for the index entry that has a key value of 1 with the rowid of the row updated by step 1.
5. Update table data (col0) for row 3 from 2 to 3.
6. Update the key data for the index entry that has a key value of 2 with the rowid of the row updated by step 3.
....
Continue associating rowid values for the index entries that correspond to the new table values until the final row is to be updated.
....n. After the last row has been updated to a key value that is not already represented by a current index entry, insert a leaf row with the address of the table row updated by step n-1.

Viola! Oracle prevented index key value collisions by taking special measures. This explains what Oracle has done and how Oracle has accomplished it. But, how did Oracle know to do this? Certainly the execution plan for this statement does not give us any insight into Oracle’s index maintenance stategy. It is doing a full table scan on the test table as expected and index maintenance is under the covers. However, this does not mean that the code does not behave differently when parsed under different “conditions.” For example, what if there was not a unique index on COL1, but instead a non-unique index and no primary key constraint. Would that change Oracle’s approach to index maintenance? It turns out that it does indeed. If the conditions are arranged as just mentioned, Oracle performs its "typical" index update maintenance (delete leaf row followed by an insert leaf row) taking no special measures to account for potential index key collisions. Why should it? There isn’t any data condition (i.e. unique index) to tell Oracle to take any precautions.

Wait, have we answered the question “how did Oracle know to do this”? It certainly appears so. Oracle was aware that I was attempting to update the key value associated with a unique index and took an optimistic approach to the index maintenance; giving me the benefit of the doubt that the resultant index entries would still be unique. Oracle did not evaluate my SQL statement and devise an index maintenance plan that was “friendly” based on the excellent prospect that COL1=COL1+1 would ultimately generate a set of unique entries. To verify this I tested the same behavior with the update condition COL1=mod(COL1,2) and Oracle took the same "friendly" approach. That is, Oracle updated 5 table rows before failing. It wanted to accommodate but apparently a tolerance threshold was met. The index and table maintenance in this test was the following:


1. No action was required on index entries for the first two table row modifications. Oracle was okay with modifying 0->0 and 1-> 1 without affecting the index. That is pretty cool!
2. On the third row COL1 was updated in the table to a value of 0. Well, that was a collision so Oracle only deleted the leaf row associated with the key value of 2 (third table row).
3. On the fourth row COL1 was updated in the table to a value of 1. Once again this was a collision so Oracle only deleted the leaf row associated with the key value of 3 (fourth row).
4. On the fifth row COL1 was updated in the table to a value of 0. On this collision Oracle has seemingly "had enough" and rolled back the transaction after failing with an ORA-00001.

In summary, Oracle has simply taken the code path associated with maintaining a unique index given the current execution plan. No fortune telling or precognition, just good schema awareness.