• 索引整理


    几种常用的在线索引整理方式:
    (1) alter index rebuild
    (2) alter index rebuild online
    (3) alter index coalesce
    (4) alter index shrink space
    (5) alter index shrink space compact
    (6) alter index shrink space cascade

    这里简单做一下说明:
    alter index rebuild
    扫描现有索引块进行索引重建,会下降索引高水位,一般执行速度快,但会全程对表加锁,阻塞DML操作。
    Can use the existing index to create the new version. Can be optimized for reduced overheads.
    Locks the table for the duration of the rebuild. "Doubles" space usage temporarily. May require massive sorts. Can "cause" Oracle error 01410.

    alter index rebuild online
    扫描全表进行索引重建,会下降索引高水位,一般执行速度慢,只在开始和结束时对表加锁,执行中间不阻塞DML操作。
    Does not lock table for entire rebuild. Can be optimized for minimal overheads.
    Locks table at start and end of rebuild. Cannot use the index to rebuild the index. "Doubles" space usage temporarily.
    Adds row-level trigger to table actions. May require massive sorts. Can "cause" Oracle error 01410.

    alter index coalesce
    ● 对索引块做合并操作,不会下降索引高水位,可随时中断。全程不阻塞DML操作。
    ● 操作后页块数量下降,而branch枝块和root根块的结构是不会变化的。
    ● 并不释放索引上的多余空间,但索引结构实际占用的空间BTREE_SPACE下降。
    ● 不会导致索引BLEVLE降级。
    Completely "online" process as it doesn't do any table locking. Repacks within existing index structure.
    Can generate a lot of redo. Not very aggressive about repacking so only useful for special cases (until 10g). Can 'cause' ORA-01555 errors

    alter index shrink space
    ● 收缩索引并降低高水位。在结束时对表加锁,执行中间不阻塞DML操作。
    ● REDO空间消耗比COALESCE高。

    alter index shrink space compact
    收缩索引不降低高水位。全程不阻塞DML操作。

    alter index shrink space cascade
    收缩索引同时收缩表,一般不怎么使用。在结束时对表加锁,执行中间不阻塞DML操作。

  • 相关阅读:
    复杂业务
    重析业务逻辑架构模式
    Katana介绍以及使用
    使用ServiceStack构建Web服务
    ASP.NET vNext 在 Mac OS
    用户端的防腐层作用及设计
    Mvc 模块化开发
    编程语言
    页面生命周期
    If you pay peanuts,you get monkeys
  • 原文地址:https://www.cnblogs.com/roger112/p/16290218.html
Copyright © 2020-2023  润新知