索引键值更新的问题

traveldba

> 2。修改索引块的时候，上面3个索引被修改的顺序是 test1_1 --〉test1_2--->test1_3么？还说不确定的？
从redo 记录上看 书从obeject_id 大的开始更新

Yong Huang

traveldba 发表于 2013-9-21 00:34
> 2。修改索引块的时候，上面3个索引被修改的顺序是 test1_1 --〉test1_2--->test1_3么？还说不确定的？
从 ...

Thanks for checking redo log. Are you sure it's the index with the largest Object ID that is inserted into first? I did a SQL trace (event 10046 level 12) on both 11.2.0.3 and 12c and it shows the insert starts from the smallest Object ID. A simple insert shows wait events 'db file sequential read' for the table, the index created first, then the index created later. Their object ID's are going up in this order.

To confirm, I drop the first index and re-create it so its object_id becomes larger than that for the second index. SQL trace again. Now this re-created index shows last:

insert into t values (123, 456)
END OF STMT
PARSE #140689089782360:c=3000,e=3599,p=0,cr=8,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1379968895824349
WAIT #140689089782360: nam='db file sequential read' ela= 536 file#=4 block#=129 blocks=1 obj#=118296 tim=1379968895825356
WAIT #140689089782360: nam='db file sequential read' ela= 216 file#=4 block#=128 blocks=1 obj#=118296 tim=1379968895825838
WAIT #140689089782360: nam='db file sequential read' ela= 214 file#=1 block#=102169 blocks=1 obj#=118298 tim=1379968895826640
WAIT #140689089782360: nam='db file sequential read' ela= 183 file#=4 block#=139 blocks=1 obj#=118318 tim=1379968895827106

SQL> select object_name, object_type, object_id, created from user_objects where object_name in ('T', 'T_I', 'T_I2') order by 4;

OBJEC OBJECT_TYPE          OBJECT_ID CREATED
----- ------------------- ---------- -----------------
T     TABLE                   118296 20130923 15:25:21
T_I2  INDEX                   118298 20130923 15:25:34
T_I   INDEX                   118318 20130923 15:41:04  <-- this index is re-created the second time around

The SQLs I run are simply:

create table t (x int, y int);
create index t_i on t (x);
create index t_i2 on t (y) tablespace system;
alter session set events '10046 trace name context level 12';
insert into t values (123, 456);
commit;
alter session set events '10046 trace name context off';
--then re-create t_i and SQL trace again

(The first time I do it, the SQL trace shows management of deferred segment creation. The trace file is harder to read. I could have disabled the feature first, or just truncate table and insert again. But the first trace file does show one interesting point: update of seg$, which, if you read it carefully, shows that the 3 segments were processed in the order of T, T_I, T_I2. To see this effect, it's easier to create one index in a different tablespace, such as system.)

traveldba

Yong Huang 发表于 2013-9-24 04:40
Thanks for checking redo log. Are you sure it's the index with the largest Object ID that is inser ...

我现在也不敢确定，10046 看到的只是读，所以没办法猜测！可以尝试下吧object 小的对象重建。在看下redo信息。