oracle 索引什么时候重建和重建方法讨论
作者&投稿：诺振 （若有异议请与网页底部的电邮联系）
失效的时候重建。。
一般来说在你做了truncate操作之后建议重建下索引，一般情况不太会索引失效
定期每个礼拜做一次就可以了
但是统计信息会在数据更新后经常变，需要重新分析索引信息，这个要经常做
1. 应该是可行的， 具体 会不会节省时间 试一下就可以了。
2. 大概每个月存储四五十万的数据，里面只保存最新四个月的数据
每次create这7个索引用时都特别长，大概需要三四个小时；
200万的数据，重建索引花费的时间太长了；很奇怪。
3. 估计之前的 先drop掉索引，然后插入数据完毕后create索引 也是为了避免 插入数据时，索引对插入效率的影响。
oracle 索引什么时候重建和重建方法讨论
分类：数据库技术 字号： 大大中中小小 索引什么时候需要重建和重建的方法
一提到索引，大家都知道，但是怎样建索引，什么时候重建索引，重建索引用什么方法，可能有的就不太清楚了，我根据一些资料简单的整理一点，如果哪里不对或是不妥请大家指点，希望大家有更好经验也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_indexeswhere blevel>=4.
另一个从重建中受益的指标显然是当该索引中的被删除项占总的项数的百分比。如果在20%以上时，也应当重建，如下
SQL>anlyze index ------ validatestructure
SQL>select(del_lf_rows_len/lf_rows_len)*100 from index_stats where 删除并从头开始建立索引。
b. 使用alter index -------- rebuild 命令重建索引
c. 使用alter index -------- coalesce命令重建索引。
下面讨论一下这三种方法的优缺点：
1).删除并从头开始建索引：方法是最慢的，最耗时的。一般不建议。
2).Alter index ---- rebuild 快速重建索引的一种有效的办法，因为使用现有索引项来重建新索引，如果客户操作时有其他用户在对这个表操作，尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题，alter index ------- rebuild online.但是,由于新旧索引在建立时同时存在，因此，使用这种技巧则需要有额外的磁盘空间可临时使用，当索引建完后把老索引删除，如果没有成功，也不会影响原来的索引。利用这种办法可以用来将一个索引以到新的表空间。
Alter index ------ rebuild tablespace -----。
这个命令的执行步骤如下：
首先，逐一读取现有索引，以获取索引的关键字。
其次，按新的结构填写临时数据段。
最后，一旦操作成功，删除原有索引树，降临时数据段重命名为新的索引。
需要注意的是alterindex ---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 ontable_name(column_name)
tablespace tablespace_name
compress;
另一种方法：
SQL>alter index index_name rebuildcompress;
3． itmap(位图)索引。
B树索引在数据具有高基数的列工作的最好，对于低基数的列，位图索引可能是更有效的选择。位图索引创建表行的一个二进制映像，并把映像存储在索引块中，这种类型的索引的DML操作少，长度大并且含有极少不同的值得列特别有用。位图索引不应当用在频繁发生insert,update,delete操作的表上，这些dml操作在性能方面的代价很高，因为，他们会引起位图级的加锁发生，而且要求动态的重建所有可能值的位图。为图索引最适合数据仓库和决策支持系统。
4．基于函数的索引
当把一个函数运用于被索引的列上时，该列德索引都变得无效，基于函数的索引就是为了解决这个问题。
5．反向键索引
是一种特殊类型的B树索引，在索引基于含有序数的列时使非常有用的，如果一个传统的B树索引基于一个含有这种数据的列，往往会产生许多级，由于B树索引有 4级以上的深度会降低性能，因此反向键索引更适合这种类型，反向键索引通过简单的烦象被索引的列中的数据来解决问题，他首先反向每个列键值的字节，然后在反向后的新数据上进行索引，而新数据在值的范围上的分布通常比原来的有序数更均匀。
6．索引组织表
由于B树、位图、反向键索引的使用而引起的性能将会导致这样的事实，这些索引中的项目直接指向索引基表中对应数据的行ID,这是从表行没有按任何特定的顺序来物理地存储表中检索表行的一种有效方法，这种表叫做堆表，oracle大多数表中以一种堆叠方式存储行数据，因为行以一种或多或少的随机方式被分配给表内的块，之所以出现这种随机性，是因为oracle在决定把一个行存储在何处时并不考虑改行的内容，oracle只是把该行存储在它从该表的freelist 上所发现的第一个块中。
如果希望按一种指定顺序来存储一个表的数据，就不能使用堆表，为此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:致使在创建索引组织表的位图索引时所必需的一个关联映像表的创建。
以上是我根据一些资料对索引的一个简单阐述，大家可能有不同的见解，希望对大家有帮助，那些不妥的地方还希望大家提出来。
参考资料：ocp困惑racle9i性能调整
oracle statspack 高性能调整技术
[@more@]
analyze index t_id_ind validate structure
select (del_lf_rows_len/lf_rows_len)*100 from index_stats
>20%
b. 使用alter index t_id_ind rebuild 命令重建索引
c. 使用alter index t_id_ind coalesce命令重建索引。
alter indext_id_ind rebuild online.
但是,由于新旧索引在建立时同时存在，因此，使用这种技巧则需要有额外的磁盘空间可临时使用，当索引建完后把老索引删除，如果没有成功，也不会影响原来的索引。利用这种办法可以用来将一个索引以到新的表空间。
Alter index ------ rebuild tablespace -----。
这个命令的执行步骤如下：
首先，逐一读取现有索引，以获取索引的关键字。
其次，按新的结构填写临时数据段。
最后，一旦操作成功，删除原有索引树，降临时数据段重命名为新的索引。
需要注意的是alter index ---rebuild 命令中必须使用tablespace字句，以保证重建工作是在现有索引相同的表空间进行
alter index ----- coalesce 使用带有coalesce参数时重建期间不需要额外空间，它只是在重建索引时将处于同一个索引分支内的叶块拼合起来，这最大限度的减少了与查询过程中相关的潜在的加锁问题，但是，coalesce选项不能用来讲一个索引转移到其他表空间
裘菡余甘： Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4 oracle 索引什么时候重建和重建方法讨论 分类:数据库技术 字号: 大大中中小小 索引什么时候需要重建和重建的方法 一提到索引,大家都知道,但是怎样建索引,什么时候重建索...
隆回县19661153073： Oracle分区索引什么情况下会重建 - ？
裘菡余甘： 1.上述的语句是对单个分区索引进行重建.2.索引分区的意思是指建立一个分区索引,而不是一个普通索引.3.重建分区索引,这个比如说碎片太多,想换个表空间都可以重建的.4.执行完了,就表示执行成功了.索引的分区信息可以在 user_ind_partitions 查询!
隆回县19661153073： oracle数据库如何重建索引 - ？
裘菡余甘： 用rebuile语句即可啊 Alter indexindex_name rebuild; Alter indexindex_name rebuild online;也可以把索引删除了重新建立 drop index indexindex_name; create index indexindex_name on table_name(col_name);
隆回县19661153073： oracle 怎么看需要重建索引 - ？
裘菡余甘： oracle 怎么看需要重建索引 select * from user_indexes where table_name = 'student' create index i_student_num on student(num)
隆回县19661153073： oracle 数据库 索引有必要定时重建吗 - ？
裘菡余甘： 索引重建时会做两个工作:1,空间重新分配,2,统计信息从新收集.对于空间足够前者可以忽略 对于后者,不仅索引的统计信息需要重新收集,表的统计信息也需要收集.高版本oracle 会自动收集统计信息.如果执行计划明显异常时,可以手工收集统计信息,采样率高些.正常不用重建索引.
隆回县19661153073： oracle索引什么时候失效 - ？
裘菡余甘： 容易引起oracle索引失效的原因很多: 1、在索引列上使用函数.如SUBSTR,DECODE,INSTR等,对索引列进行运算.需要建立函数索引就可以解决了. 2、新建的表还没来得及生成统计信息,分析一下就好了 3、基于cost的成本分析,访问的表...
隆回县19661153073： 有办法查看oracle的某个索引上次被重建的日期么? - ？
裘菡余甘： 索引上次重建日期可以从 user_objects 数据字典视图中得到,不需要 dba 权限.例如您的索引是 your_index, 你想查改所用最近被重建的时间:select OBJECT_NAME, timestamp, LAST_DDL_TIME from usr_objects where OBJECT_NAME='YOUR_INDEX' and object_type='INDEX';其中 last_ddl_time 就是该索引最近一次被重建的时间
隆回县19661153073： oracle 重建索引 - ？
裘菡余甘： 1、重新收集统计信息 analyze table 表名 compute statistics for table for all indexes for all indexed columns analyze index 索引名 compute statistics 2、把表现不同的sql及其执行计划发上来看看
隆回县19661153073： oracle中如何重建索引? - ？
裘菡余甘： alter index ind_id_idx rebuild;--重建索引 select if_rows,if_rows_len,del_id_rows_len from index_stats; .....
隆回县19661153073： 分析索引与重建主键索引 - ？
裘菡余甘： validate structure应该是用来填充index_stats视图,而compute statistics 填充user_indexes(这个视图在后来oracle建议使用user_ind_statistics来代替),前者注重结构统计信息,而后者一般是对整个索引的状态进行统计(根据两个视图的不同字段可以看出区别).主键索引和其它索引没有什么区别,只是列上加了一个unique约束和一个unique索引而已,所以分析索引只要和其它列上索引一样分析就行了.
