oracle rebuild online,alter index rebuild

Oracle alter index rebuild 说明[日期：2011-06-12]来源：Linux社区 作者：tianlesoftware[字体：大中小]

一.官网说明

在MOS 上的一篇文章讲到了rebuild online 和offline的区别：Index Rebuild Is Hanging Or Taking Too Long [ID 272762.1]Symptoms:

=========

Performance issues while rebuilding very large indexes. The offline rebuilds of their index is relatively quick -finishes in 15 minutes. Issuing index rebuild ONLINE statement => finishes in about an hour. This behavior of ONLINE index rebuilds makes it a non-option for large tables as it just takes too long to scan the table to rebuild the index. The offline may not be feasible due to due to the 24/7 nature of the database. This may be a loss of functionality for such situations. If we attempt to simultaneously ONLINE rebuild the same indexes we may encounter hanging behavior indefinitely (or more than 6 hours).

DIAGNOSTIC ANALYSIS:

--------------------

We can trace the sessions rebuilding the indexes with 10046 level 12. Comparing the IO reads for the index-rebuild and the index-rebuild-online reveals the following:

ONLINE index rebuilds：It scans the base table and it doesn't scan the blocks of the index.

OFFLINE index rebuilds：It scans the index for the build operation.This behaviour is across all versions.Cause/Explanation

=============

When you rebuild index online, it will do a full table scan 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.

-- rebuild index online的时候，会选择全表扫描，同时会维护一个中间日志表，用来记录在rebuild期间的增量数据，原理类似于物化视图日志，日志表是一个索引组织表(IOT)，这张中间表只有插入，不会有删除和修改操作，而且只有主键条件查询，正是IOT最合适的场景。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. --rebuild offline时，选择的6模式的X锁，它根据old index来rebuild。因此不允许进行DML，也就没有中间表。因此也比较块。Solution/Conclusion:

===========

- The ONLINE index rebuild reads the base table, and this is by design.

- Rebuilding index ONLINE is pretty slow.

- Rebuilding index offline is very fast, but it prevents any DML on the base table.

二.rebuild index说明有关锁的模式信息如下：锁模式锁描述解释SQL操作

0none

1NULL空Select

2SS(Row-S)行级共享锁，其他对象只能查询这些数据行Select for update、Lock for update、Lock row share

3SX(Row-X)行级排它锁，在提交前不允许做DML操作Insert、Update、 Delete、Lock row share

4S(Share)共享锁: 阻止其他DML操作Create index、Lock share

5SSX(S/Row-X)共享行级排它锁：阻止其他事务操作Lock share row exclusive

6X(Exclusive)排它锁：独立访问使用Alter table、Drop able、Drop index、Truncate table 、Lock exclusive

DML操作一般要加两个锁，一个是对表加模式为3的TM锁，一个是对数据行的模式为6的TX锁。只要操作的不是同一行数据，是互不阻塞的。在rebuild index online 的开始和结束阶段时，需要短暂的对表持有模式为4的TM锁的，当获取到4级别的锁之后，才降为2级。如果rebuild online一直没获取到4级别的锁,那么相关的DML全部产生等待。在执行期间只持有模式2的TM锁，不会阻塞DML操作。在Oracle 11g之后,oracle做了特殊处理,后续的dml不会被rebuild online的4级别锁阻塞.所以如果在执行rebuild index online前长事务，并且并发量比较大，则一旦执行alter index rebuild online，可能因为长事务阻塞，可能导致系统瞬间出现大量的锁，对于压力比较大的系统，这是一个不小的风险。这是需要迅速找出导致阻塞的会话kill掉，rebuild index online一旦执行，不可轻易中断，否则可能遇到ORA-08104。MOS 的文档：Session Was Killed During The Rebuild Of Index ORA-08104 [ID 375856.1]While running an online index rebuild your sessionwas killed or otherwise terminated abnormally. You are now attempting to run the index rebuild again and is throwing the error:

ORA-08104: this index object ##### is being online built or rebuilt

根据以上说明，我们可以知道在进行online rebuild的时候，Oracle会修改如下信息：

(1)修改ind$中索引的flags，将该flags+512. 关于这个flags的含义，在下面的实验中进行说明。

(2)在该用户下创建一个journal table 来保存在rebuild期间的增量数据。

该表明名称: sys_journal_.如果异常结束online rebuild操作，那么oracle就没及时清理journal table和ind$的flags标志位，系统会认为online rebuild还在操作。当然SMON 进程会来处理这些临时段。在maclean 同学(10g,11g OCM)的Blog里提到了功能：对于永久表空间上的temporary segment，SMON会三分钟清理一次(前提是接到post)，如果SMON过于繁忙那么可能temporary segment长期不被清理。temporary segment长期不被清理可能造成一个典型的问题是:在rebuild index online失败后，后续执行的rebuild index命令要求之前产生的temporary segment已被cleanup，如果cleanup没有完成那么就需要一直等下去。如果SMON 不能及时清理，在操作时就会报ORA-08104的错误。在Oracle10gR2中可以使用dbms_repair.online_index_clean手工清理这些信息，在Oracle 9i下，需要打Bug 3805539后才可以使用该工具。

手工处理的步骤如下：

(1)先查看ind$ flags 标志，如果不正确，就减去512.sql>update ind$ set flags=flags-512 where obj#=;

(2)drop journal table，这个步骤可能会报资源忙，因为有大量的日志正在插入，可以反复重试一下。sql>drop table .sys_journal_;

注意：这个步骤不能反，如果先删除sys_journal_临时表，然后再修改index的flags状态，则会报出ora-600 [4610]号错误，即数据字典不一致的错误。官

网关于dbms_repair.online_index_clean的说明：ONLINE_INDEX_CLEAN Function

This function performs a manual cleanup of failed or interrupted online index builds or rebuilds. This action is also performed periodically by SMON, regardless of user-initiated cleanup.This function returns TRUE if all indexes specified were cleaned up and FALSE if one or more indexes could not be cleaned up.SyntaxDBMS_REPAIR.ONLINE_INDEX_CLEAN (object_idIN BINARY_INTEGER DEFAULT ALL_INDEX_ID,wait_for_lockIN BINARY_INTEGER DEFAULT LOCK_WAIT)RETURN BOOLEAN;ParametersParameterDescriptionobject_idObject id of index to be cleaned up. The default cleans up all object ids that qualify.

wait_for_lockThis parameter specifies whether to try getting DML locks on underlying table [[sub]partition] object. The default retries up to an internal retry limit, after which the lock get will give up. If LOCK_NOWAIT is specified, then the lock get does not retry.

因此在做rebuild index online的时候，一定要在开始和结束阶段观察系统中是否有长事务的存储，对于并发量较大的系统，最严重的后果，可能在这两个关键点导致数据库产生大量锁等待，系统负载飙升，甚至宕机。

analyzeindex

analyzeindex**** validate structure  ——分析是否要重建索引，或者索引块有没有坏。分析完后index_stats就有结果了,你可以根据index_stats中的结果来分析是否要rebuild索引

Analyze index**** compute statistics —— 收集统计数据。

在analyze index 的时候需要两个命令，分别是analyzeindex index_name validate structure. 和analyze index index_name compute statistics

Analyze index validate structure 命令,  analyze index index1 validate structure：是用来分析索引的数据块是否有坏块，以及根据分析得到的数据(存放在index_stats)來判断索引是否需要重新建立。

什么样的index需要rebuild？

当一个table经常进行DML操作时，它的索引会存在许多block空间的浪费，这是因为index block中的记录只有在全部表示为不可用时， block 才能被加入到freelist中去被重新利用。所以我们需要寻找那些浪费空间很严重的index。

方法是: 1) analyze index index_name validate structure;

2) select del_lf_blk_len/lf_blk_len from index_stats where name = :index_name;

3) 如果结果大于20%， 那你的Index就可以被rebuild了。

validate structure有二中模式： online, offline， 默认是offline模式。以offline模式分析时， 会對表加一个4级別的锁(表共享)，对run系統可能造成一定的影响。

而online模式则没有表lock的影响，但当以online模式分析时， 在视图index_stats没有统计信息。

analyze indexindex1 compute statistics：是用来统计index的分析信息，来为CBO服务的。从9i开始，Oracle以建议使用dbms_stats package代替 analyze

索引什么时候需要重建和重建的方法 by QYL

一提到索引，大家都知道，但是怎样建索引，什么时候重建索引，重建索引用什么方法，可能有的就不太清楚了，我根据一些资料简单的整理一点，如果哪里不对或是不妥请大家指点，希望大家有更好经验也share出来。

索引的目的是为了加快寻找数据的速度，但是如果对表经常做改动，则索引也会相应改动，时间长了，查询速度的效率就会降低，就有可能要重建索引，那么什么时候需要重建索引和用什么方法重建索引可能是大家关心的。

一.        索引在内部进行自身的管理以确保对数据行的快速访问。但是数据表中大量的活动会导致oracle索引动态地对自身的进行重新配置，这些配置包括三个方面：

1．        索引分割

当新数据行产生的索引节点要建立在现有级别上时，出现此动作。

2．        索引生成

在某些位置，索引达到此级索引的最大容量的时候，就会生成更深一级的索引结构。

3．        索引节点的删除

你可能了解到，删除表中的数据行后，索引中相应的节点不会从物理意义上删除，也没有从索引中删除此项目。而是从逻辑上删除此索引项目，并在索引树中留下了一个“死“节点，当索引删除了叶节点或是生成了过深的的级别层次后，就需要进行重建。

二  索引的种类：

a.B-tree(B树)索引

b.压缩B树索引

c.Bitmap(位图)索引

d.函数索引

e.Reverse Key Index(反向键索引)

f.Index Organized Table(索引组织表)

三 下面分别对各种索引进行说明

在进行介绍前先说明几个术语：

高基数：简单理解就是表中列的不同值多

低基数：建单理解就是表中的列的不同值少

以删除的叶节点数量：指得是数据行的delete操作从逻辑上删除的索引节点的数量，要记住oracle在删除数据行后，将“死“节点保留在索引中，这样做可以加快sql删除操作的速度，因此oracle删除数据行后可以不必重新平衡索引。

索引高度：索引高度是指由于数据行的插入操作而产生的索引层数，当表中添加大量数据时，oracle将生成索引的新层次以适应加入的数据行，因此，oracle索引可能有4层，但是这只会出现在索引数中产生大量插入操作的区域。Oracle索引的三层结构可以支持数百万的项目，而具备4层或是更多层的需要重建。

每次索引访问的读取数：是指利用索引读取一数据行时所需要的逻辑I/O操作数，逻辑读取不必是物理读取，因为索引的许多内容已经保存在数据缓冲区，然而，任何数据大于10的索引都需要重建。

1.        B-tree(B树)索引

是现代关系型数据库中最常用的索引。除了存储索引数据外，还存储一个行ID，用来指出该行其余数据存储在这个被索引表中的什么地方。该索引以一种数结构格式存储这些值。

Oracle建议如果表经过排序，当返回40%一下的数据时使用索引，如果高于40%则使用全表扫描，如果没有经过排序，则当返回7%以下时，使用索引。看表是否排序，可以看dba_indexes字典中的CLUSTERING_FACTOR列，如果与表占用的数据块数相近，则经过了排序，如果与行数相近，则没有排序。那么什么时候重建呢？我们可以利用analyze index …….. compute statistics 对表进行分析。然后察看dba_indexes中的blevel。这列是说明索引从根块到叶快的级别，或是深度。如果级别大于等于4。则需要重建，如下：

Select index_name,blevel from dba_indexes where blevel>=4.

另一个从重建中受益的指标显然是当该索引中的被删除项占总的项数的百分比。如果在20%以上时，也应当重建，如下

SQL>anlyze index ------ validate structure

SQL>select (del_lf_rows_len/lf_rows_len)*100 from index_stats where name=’------‘

就能看到是否这个索引被删除的百分比。

上面只是判断，那么，怎样重建会更好呢？

建索引的办法：

a.        删除并从头开始建立索引。

b.        使用alter index -------- rebuild 命令重建索引

c.        使用alter index -------- coalesce命令重建索引。

下面讨论一下这三种方法的优缺点：

1).删除并从头开始建索引：方法是最慢的，最耗时的。一般不建议。

2).Alter index ---- rebuild 快速重建索引的一种有效的办法，因为使用现有索引项来重建新索引，如果客户操作时有其他用户在对这个表操作，尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题，alter index ------- rebuild online.但是,由于新旧索引在建立时同时存在，因此，使用这种技巧则需要有额外的磁盘空间可临时使用，当索引建完后把老索引删除，如果没有成功，也不会影响原来的索引。利用这种办法可以用来将一个索引以到新的表空间。

Alter index ------ rebuild  tablespace -----。

这个命令的执行步骤如下：

首先，逐一读取现有索引，以获取索引的关键字。

其次，按新的结构填写临时数据段。

最后，一旦操作成功，删除原有索引树，降临时数据段重命名为新的索引。

需要注意的是alter index ---rebuild 命令中必须使用tablespace字句，以保证重建工作是在现有索引相同的表空间进行。

3).alter index ----- coalesce 使用带有coalesce参数时重建期间不需要额外空间，它只是在重建索引时将处于同一个索引分支内的叶块拼合起来，这最大限度的减少了与查询过程中相关的潜在的加锁问题，但是，coalesce选项不能用来讲一个索引转移到其他表空间。

2．压缩B树索引

当B树索引基于大表时，尤其是当基于数据仓库或决策支持系统中的大表时，这些索引会耗费大量的存储空间，压缩(compressed)B树索引用来最大限度的减少某些类型的B树索引使用的空间。当一个B树索引得到压缩时，被索引的猎的重复出现就被消除掉，进而减少了存储索引的总的存储空间。例如：

压缩前：smith每次出现还要存储它的相关的rowid.

姓        关联rowid

smith        AAABSOAAEAAAABTAAB

smith        AAABSOAAEAAAABTAAC

smith        AAABSOAAEAAAABTAAD

压缩后:smith项和rowid指存储一次。

smith        AAABSOAAEAAAABTAAB, AAABSOAAEAAAABTAAB, AAABSOAAEAAAABTAAB

创建方法：

SQL>create index index_name on  table_name(column_name)

tablespace tablespace_name

compress;

另一种方法：

SQL>alter index index_name rebuild compress;

3．        itmap(位图)索引。

B树索引在数据具有高基数的列工作的最好，对于低基数的列，位图索引可能是更有效的选择。位图索引创建表行的一个二进制映像，并把映像存储在索引块中，这种类型的索引的DML操作少，长度大并且含有极少不同的值得列特别有用。位图索引不应当用在频繁发生insert,update,delete操作的表上，这些dml操作在性能方面的代价很高，因为，他们会引起位图级的加锁发生，而且要求动态的重建所有可能值的位图。为图索引最适合数据仓库和决策支持系统。

4．        基于函数的索引

当把一个函数运用于被索引的列上时，该列德索引都变得无效，基于函数的索引就是为了解决这个问题。

5．        反向键索引

是一种特殊类型的B树索引，在索引基于含有序数的列时使非常有用的，如果一个传统的B树索引基于一个含有这种数据的列，往往会产生许多级，由于B树索引有4级以上的深度会降低性能，因此反向键索引更适合这种类型，反向键索引通过简单的烦象被索引的列中的数据来解决问题，他首先反向每个列键值的字节，然后在反向后的新数据上进行索引，而新数据在值的范围上的分布通常比原来的有序数更均匀。

6．        索引组织表

由于B树、位图、反向键索引的使用而引起的性能将会导致这样的事实，这些索引中的项目直接指向索引基表中对应数据的行ID,这是从表行没有按任何特定的顺序来物理地存储表中检索表行的一种有效方法，这种表叫做堆表，oracle大多数表中以一种堆叠方式存储行数据，因为行以一种或多或少的随机方式被分配给表内的块，之所以出现这种随机性，是因为oracle在决定把一个行存储在何处时并不考虑改行的内容，oracle只是把该行存储在它从该表的free list 上所发现的第一个块中。

如果希望按一种指定顺序来存储一个表的数据，就不能使用堆表，为此oracle提供了索引组织表，索引组织表不是存储一个指向行数据的其余部分存储在了何处的行的ID指针，而是把行数据全部存储在索引本身内，这产生了两个性能好处：

n        表行按索引顺序来存储。

n        使用B树索引时引起的先读取索引后读取表锁使用的额外I/O操作得到消除。

例如：

sql>create table emp

(last_name varchar2(9)  primary key,

first_name varchar2(9),

hire_date date)

organization index tablespace users

pctthreshold 25

including first name

overflow tablespace qyl

mapping table;

所有索引组织表在将要作为索引基础的那一列上都必须有一个主键约束，索引组织表不能含有唯一性约束或是被聚簇。

下面说明各个参数的含义：

organization index：说明该表是索引组织表

pctthreshold     :指定整个数据块的什么百分比要保持打开，以便存储一个与主键值相关联的行数据，其中主键值必须在0到50之间(50是默认值)

including : 指定在行长度超过pctthershold中所设置的大小时按那一列 把行分解成两段

overflow tablespace ：指定在行长度超过pctthreshold中设置的大小时行数的的另一部分存储到的表空间。

Mapping table:致使在创建索引组织表的位图索引时所必需的一个关联映像表的创建。

come from http://www.itpub.net/thread-94564-1-1.html