ITPub博客

首页 > Linux操作系统 > Linux操作系统 > rebuild index online or offline

rebuild index online or offline

原创 Linux操作系统 作者:guo_li_yuan 时间:2008-01-04 13:53:10 0 删除 编辑

When you rebuild index online,
- it will do a full tablescan on the base table.
- At the same time it will maintain a journal table for DML data, which has
changed during this index rebuilding operation.
So it should take longer time, specially if you do lots of DML on the same table,
while rebuilding index online.

On the other hand(另一面), while rebuilding the index without online option, Oracle will grab
the index in X-mode and rebuild a new index segment by selecting the data from
the old index. So here we are
- not allowing any DML on the table hence there is no journal table involved
- and it is doing an index scan
Hence it will be pretty fast.

Online Index Rebuild Features:
+ ALTER INDEX REBUILD ONLINE;
+ DMLs are allowed on the base table
+ It is comparatively Slow
+ Base table is referred for the new index
+ Base table is locked in shared mode and DDLs are not possible
+ Intermediate table stores the data changes in the base table, during the index rebuild to update the new index later

Offline Index Rebuild Features:
+ ALTER INDEX REBUILD; (Default)
+ Does not refer the base table and the base table is exclusively locked
+ New index is created from the old index
+ No DML and DDL possible on the base table
+ Comparatively faster

So, the base table is not referred for data when the index is rebuilt offline.
This article describes this behavior. with test cases and depicts a few scenarios when this is violated

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/377444/viewspace-90045/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-01-04

  • 博文量
    11
  • 访问量
    18329