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.

coComments to follow the comments

From Eddie Awad i have read about a new cool service called coComments. Tracking my comments and people’s comments after that has always been a problem for me. This service gives you a single page to see all your comments you made to all the blogs you read. You put a bookmarklet to your browser and click on it when you are making a comment on a blog. From your coComments page you can view all the comments and also the comments made by other people after your comment (of course if they are also using coComments). You can register at coComments site, it is invitation based, Eddie Awad’s post has some invitation codes for that, so be quick if you want to use them as they can be used only once.

If you are a blog writer you can also put a small code in your blog and show your comments to the other blogs on your own page. I have placed this above the links section on the right. And you can also get an rss feed that tracks all the comments from the blog that you comment on.

Cool…