Question

oracle 索引什么时候重建和重建方法讨论

Answer 1

Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4
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选项不能用来讲一个索引转移到其他表空间