Re: row migaration

On Tue, 27 Dec 2005 21:52:15 -0500, Serge Rielau wrote:

> When are the tumbstones/forward pointers cleaned up?

Actually, they're not. One can detect them, by using ANALYZE TABLE LIST CHAINED ROWS command but they're not cleaned by themselves.

> I recall a lot of
> debate recently about how Oracle doesn't need reorg.

Oracle doesn't need reorg of an index. The main argument for an occasional rebuild of indexes was the myth that oracle doesn't reuse the blocks corresponding to deleted keys. It was the truth in Oracle7, no longer so in Oracle9. Tables that suffer from excessive row travels and tours will need a good reorg in Oracle, too.

> In DB2 excessive
> row-migration is fought with reorg....

Ignoring or misdiagnosing the problem also works. For instance: if a table becomes a hot spot and enormous amount of disk I/O is generated for no apparent reason, the conclusion drawn may very well be that you need to add more memory in the buffer cache and thus increase the hit ratio. It's certainly easier then running analyze and studying the problem carefully.

PS:

---
What are "tumbstones"? I know the story of Wyatt Earp, Tombstone and alike
but that seems somehow unrelated to the database group.

-- 
http://www.mgogala.com