truncate 实现机制

lcmlsj

在dba-object视图中object_id 和data_object_id的区别？
对表执行delete操作时，object_id 和data_object_id得值都不变。
对表执行truncate操作时,object_id 值不变,data_object_id得值增加2,不知什么意思，2 表示什么？

lcmlsj

select * from dba_objects
where object_name='T';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
12756 12880
truncate table t;
OBJECT_ID DATA_OBJECT_ID
---------- --------------
12756 12882

lcmlsj

查oracle文档：
OBJECT_ID NUMBER NOT NULL Dictionary object number of the object
DATA_OBJECT_ID NUMBER
Dictionary object number of the segment that contains the object



Note: OBJECT_ID and DATA_OBJECT_ID display data dictionary metadata. Do not confuse these numbers with the unique 16-byte object identifier (object ID) that the Oracle Database assigns to row objects in object tables in the system.

biti_rainy

truncate 的时候data_object_id重新生成，HWM被移动到紧靠segment header 的block。 data_object_id 的变化表示 segment 重新产生过。至于增加2，这个2并没有什么含义，只是 增大而已。

比如 一个大查询在执行，表被truncate ，当查询继续fetch 的时候将发现 block 中 data_object_id 和 新的segment 的 data_object_id 不一样了，于是返回错误。错误编号我忘记了。

lcmlsj

谢谢你 biti_rainy  master；good  wishes to you and your 柔嘉维则！
关于：比如 一个大查询在执行，表被truncate ，当查询继续fetch 的时候将发现 block 中 data_object_id 和 新的segment 的 data_object_id 不一样了，于是返回错误。错误编号我忘记了。
我准备做一下实验。

wing hong

Other  interesting info about truncate comes from this mtalink note <286363.1>
"
When a truncate/drop is issued,

1.the foreground first acquires the "RO" enqueue in exclusive mode,
2.then cross instance calls (or one call if it is a single object) are issued
(the "CI" enqueue is acquired for cross instance call)
3.The CKPT processes on each of instances requests the DBWR to write
the dirty buffers to the disk and invalidate all the clean buffers.
4. After DBWR finishes writing, the foreground process releases the RO
enqueue.

In effect this enqueue serializes the truncate/drop operations given concurrently.
"

I had experience with slow truncate due to the overloading of checkpoint process.

wing hong

another useful metalink note is <287429.1>

wing hong

And in note <286363.1> , it says " RO enqueue known as "Multiple object resue" "  . I think it should read "Multiple object reuse".  You can also find this in 10g

Yong Huang

最初由 biti_rainy 发布
[B]truncate 的时候data_object_id重新生成，HWM被移动到紧靠segment header 的block。 data_object_id 的变化表示 segment 重新产生过。至于增加2，这个2并没有什么含义，只是 增大而已。
[/B]

I'm puzzled by the increment 2. Why 2? This 2 seems to be independent of whether the table has dependent objects (indexes, etc.) or how many rows are in the table.

By the way, if the table is already empty, truncate won't increment data_object_id. But alter table move always does. The increment due to table move seems to be related to the number of blocks in the table. In this sense, we can say alter table move definitely recreates the segment but saying truncate does it too is a little far-fetched, in my opinion.

One more case where data_object_id increments is, similar to table "rebuild" or move, alter index rebuild.

Other useful things worth remembering. You use data_object_id in v$bh and x$bh, but object_id in v$locked_object.

Yong Huang

Yong Huang

最初由 wing hong 发布
[B]And in note <286363.1> , it says " RO enqueue known as "Multiple object resue" "  . I think it should read "Multiple object reuse".  You can also find this in 10g [/B]

Very glad to know you're still alive, Wing!

Yes, it's "Multiple object reuse". You can find it in v$lock_type.name where type = 'RO', and description is 'Coordinates flushing of multiple objects'.

Yong Huang