I have read at Eric S. Emrick’s blog about the redo behaviour seen when updating a primary key column Oracle takes some special steps to prevent unique key collisions that may temporarily occur during the update. You can read what it does at Eric’s post in detail. As Eric shows, it maintains the index entries while doing the update, something like; update the table row, process the index leaf, update the second row, process the index leaf, etc..
I have realized that this behaviour changes in 10G R2 (i do not know about R1 as i have not tested on it). In 10G R2, it updates all the rows first without touching the index entries and changes the index entries afterwards. Here is a test case:
SQL> create table t as select rownum col1,1 col2
2 from all_objects
3 where rownum<=10;
Table created.
SQL> alter table t add primary key(col1);
Table altered.
SQL> select index_name from user_indexes where table_name = 'T';
INDEX_NAME
------------------------------
SYS_C0028217
SQL> update t set col1=col1+1;
10 rows updated.
The related log file shows that index maintenance steps come after all rows are updated:
SCN REL_FILE# DATA_BLK# DATA_OBJ# OP
---------- ---------- ---------- ---------- --------------------
2.9868E+10 0 0 0 START
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
SCN REL_FILE# DATA_BLK# DATA_OBJ# OP
---------- ---------- ---------- ---------- --------------------
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 0 COMMIT
SQL> select object_name, object_id from dba_objects where object_id in (121729,121730);
OBJECT_NAME OBJECT_ID
------------------------------ ----------
T 121729
SYS_C0028217 121730
Compare this with Eric’s test case which shows (i have also tested this on 9.2):
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
UPDATE: Please see the comments for this post, reason of the difference of the results between 9i and 10G are explained there.
Hi Yas. The second to last statement in my post on that topic was "In summary, Oracle has simply taken the code path associated with maintaining a unique index given the current execution plan."
ReplyDeleteI just tested the behavior in 10g and it is the same. "The same" meaning given the same execution plan as 9i the index maintenance was the same. This might not be the case for all 9i/10g comparisons. Try re-running your test with a FULL(t) hint. This will cause the optimizer to perform a full table scan and you should see the same mechanics as in my post. In my 10g test the execution plan was an INDEX FULL SCAN. In my 9i test the execution plan was a FULL TABLE SCAN. It makes a world of difference. I have seen index access to the data seemingly employ different maintenance strategies for multi-row DML operations.
Eric, thanks for the clarification, i have missed that point. Using a full table scan gave the same results as yours. I have updated the original post to correct this.
ReplyDeleteMerhaba,
ReplyDeleteisminizi ve blog linkinizi http://blogs.oracle.com/ altında görünce cidden hem şaşırdım hem de çok sevindim :)
elektronik posta adresinizi göremediğim için comment girmeyi düşündüm.
şimdiden çok memnun oldum, umarım yazışır hatta tanışırız.
çalışmalarınızda başarılar.
H.Tonguc YILMAZ
Turkcell Ar-Ge, Yazılım Geliştirme
tonguc.yilmaz@gmail.com
http://Tonguc.OracleTURK.org
"Learning is expensive, not more than unawareness."
0 216 458 1584