Thursday, February 23, 2006

Index maintenance of 10G

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.

3 comments:

  1. 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."

    I 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.

    ReplyDelete
  2. 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.

    ReplyDelete
  3. Merhaba,

    isminizi 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

    ReplyDelete