Thursday, February 09, 2006

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.

10 Comments:

Blogger Darin Brown said...

Very interesting behavior. This surprised me as well.

You referenced a "COL1=mod(COL1,2) test" but I cannot find this test detailed anywhere. Was that test discussed in an earlier post? I am curious how many rows Oracle actually processed before giving up. And based on that, is there a test case that would succeed if executed to completion but fails before completion due to Oracle's index optimistic approach?

2/10/2006 1:11 PM  
Blogger Eric S. Emrick said...

Sorry about that. In the throws of editing my original post (not very good at editing yet, still a blogging newbie) I omitted the explanation for the mod(COL1,2) test case. Thanks for pointing that out. I have updated the post with said explanation near the conclusion.

In answer to your second question for the operation to succeed it would have to map the current index key values onto another set of distinct keys. If it does this, theoretically it would succeed. But, if you are looking for an upperbound of duplicate rows it will handle in a "friendly" manner before failure then I don't really know. However,it appears from testing that the upperbound is a function of the number of rows between your first "duplicate" and your second "duplicate." For example, on a test table with 500 rows and an update condition of COL1=mod(COL1,100) it will handle 100 duplicates before failing. Upon hitting the second "duplicate" for key value 0 (row number 300) it failed. This stands to reason since our mod(COL1,2) failed after the second "duplicate" for 0 (row five with value of 4) was found.

2/10/2006 4:44 PM  
Blogger yas said...

Eric, which database version did you use for the tests?

2/23/2006 4:29 AM  
Blogger Eric S. Emrick said...

These tests were performed in a 9.2.0.4 database. Are you seeing different results in another version?

Regards,
Eric

2/23/2006 7:44 AM  
Blogger yas said...

Yes, they seem to have changed this in 10G. Index changes come after all table changes are made. I have posted the results for 10G here.

2/23/2006 8:53 AM  
Blogger Eric S. Emrick said...

Yas, I have added a comment to your blog explaining why 10g used a different index maintenance mechanic. It was probably the execution path difference between 9i and 10g. I can produce your 10g results in a 9i database if the execution path accesses data via the primary key. The execution path is key to the mechanic.

2/23/2006 10:37 AM  
Blogger yas said...

Eric, yes that's correct. The reason for the difference is the execution plan, using a full table scan gives the same results as yours. I have added a reference to your comment to my original post. Thanks for the correction.

2/23/2006 10:52 AM  
Blogger Eric S. Emrick said...

No problem Yas. Thanks for taking an interest in the post.

Regards,
Eric

2/23/2006 11:07 AM  
Blogger Alberto Dell'Era said...

Eric,

very interesting article, thanks for writing it - especially the mod() test case and discussion have vastly enhanced my understanding of the mechanics of PK management, now I have a better mental model (to play with).

thanks!
Alberto

9/10/2006 4:46 PM  
Blogger daspeac said...

I have heard about another way of access accdb recovery. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues

11/04/2010 2:47 PM  

Post a Comment

<< Home