本文只讨论Oracle中最常见的索引 即是B tree索引 本文中涉及的数据库版本是Oracle i
一 查看系统表中的用户索引在Oracle中 SYSTEM表是安装数据库时自动建立的 它包含数据库的全部数据字典 存储过程 包 函数和触发器的定义以及系统回滚段
一般来说 应该尽量避免在SYSTEM表中存储非SYSTEM用户的对象 因为这样会带来数据库维护和管理的很多问题 一旦SYSTEM表损坏了 只能重新生成数据库 我们可以用下面的语句来检查在SYSTEM表内有没有其他用户的索引存在
二 索引的存储情况检查select count(*) from dba_indexes where tablespace_name = SYSTEM and owner not in ( SYS SYSTEM )
Oracle为数据库中的所有数据分配逻辑结构空间 数据库空间的单位是数据块（block） 范围（extent）和段（segment）
Oracle数据块（block）是Oracle使用和分配的最小存储单位 它是由数据库建立时设置的DB_BLOCK_SIZE决定的 一旦数据库生成了 数据块的大小不能改变 要想改变只能重新建立数据库 （在Oracle i中有一些不同 不过这不在本文讨论的范围内 ）
Extent是由一组连续的block组成的 一个或多个extent组成一个segment 当一个segment中的所有空间被用完时 Oracle为它分配一个新的extent
Segment是由一个或多个extent组成的 它包含某表空间中特定逻辑存储结构的所有数据 一个段中的extent可以是不连续的 甚至可以在不同的数据文件中
一个object只能对应于一个逻辑存储的segment 我们通过查看该segment中的extent 可以看出相应object的存储情况
（ ）查看索引段中extent的数量
select segment_name count(*) from dba_extents where segment_type= INDEX and owner=UPPER( &owner ) group by segment_name /
（ ）查看表空间内的索引的扩展情况
三 索引的选择性select substr(segment_name ) SEGMENT NAME bytes count(bytes) from dba_extents where segment_name in ( select index_name from dba_indexes where tablespace_name=UPPER( &表空间 ) ) group by segment_name bytes order by segment_name /
索引的选择性是指索引列中不同值的数目与表中记录数的比 如果一个表中有 条记录 表索引列有 个不同的值 那么这个索引的选择性就是 / =
一个索引的选择性越接近于 这个索引的效率就越高
如果是使用基于cost的最优化 优化器不应该使用选择性不好的索引 如果是使用基于rule的最优化 优化器在确定执行路径时不会考虑索引的选择性（除非是唯一性索引） 并且不得不手工优化查询以避免使用非选择性的索引
确定索引的选择性 可以有两种方法 手工测量和自动测量
（ ）手工测量索引的选择性
如果要根据一个表的两列创建两列并置索引 可以用以下方法测量索引的选择性
列的选择性=不同值的数目/行的总数 /* 越接近 越好 */
select count(distinct 第一列|| % ||第二列)/count(*) from 表名
如果我们知道其中一列索引的选择性（例如其中一列是主键） 那么我们就可以知道另一列索引的选择性
手工方法的优点是在创建索引前就能评估索引的选择性
（ ）自动测量索引的选择性
如果分析一个表 也会自动分析所有表的索引
第一 为了确定一个表的确定性 就要分析表
*** yze table 表名 pute statistics
第二 确定索引里不同关键字的数目
select distinct_keys from user_indexes where table_name= 表名 and index_name= 索引名
第三 确定表中行的总数
select num_rows from user_tables where table_name= 表名
第四 索引的选择性=索引里不同关键字的数目/表中行的总数
select i distinct_keys/t num_rows from user_indexes i user_tables here i table_name= 表名 and i index_name= 索引名 and i table_name=t table_name
第五 可以查询USER_TAB_COLUMNS以了解每个列的选择性
表中所有行在该列的不同值的数目
select column_name num_distinct from user_tab_columns where table_name= 表名
列的选择性=NUM_DISTINCT/表中所有行的总数 查询USER_TAB_COLUMNS有助测量每个列的选择性 但它并不能精确地测量列的并置组合的选择性 要想测量一组列的选择性 需要采用手工方法或者根据这组列创建一个索引并重新分析表
四 确定索引的实际碎片随着数据库的使用 不可避免地对基本表进行插入 更新和删除 这样导致叶子行在索引中被删除 使该索引产生碎片 插入删除越频繁的表 索引碎片的程度也越高 碎片的产生使访问和使用该索引的I/O成本增加 碎片较高的索引必须重建以保持最佳性能
（ ）利用验证索引命令对索引进行验证
这将有价值的索引信息填入index_stats表
validate index 用户名 索引名
（ ）查询index_stats表以确定索引中删除的 未填满的叶子行的百分比
select name del_lf_rows lf_rows round((del_lf_rows/(lf_rows+ ))* ) Frag Percent from index_stats
（ ）如果索引的叶子行的碎片超过 % 考虑对索引进行重建
alter index 用户名 索引名 rebuild tablespace 表空间名 storage(initial 初始值 next 扩展值) nologging
（ ）如果出于空间或其他考虑 不能重建索引 可以整理索引
alter index用户名 索引名 coalesce
（ ）清除分析信息
五 重建索引*** yze index 用户名 索引名 delete statistics
（ ）检查需要重建的索引
根据以下几方面进行检查 确定需要重建的索引
第一 查看SYSTEM表空间中的用户索引
为了避免数据字典的碎片出现 要尽量避免在SYSTEM表空间出现用户的表和索引
select index_name from dba_indexes where tablespace_name= SYSTEM and owner not in ( SYS SYSTEM )
第二 确保用户的表和索引不在同一表空间内
表和索引对象的第一个规则是把表和索引分离 把表和相应的索引建立在不同的表空间中 最好在不同的磁盘上 这样可以避免在数据管理和查询时出现的许多I/O冲突
set linesize col OWNER format a col INDEX format a col TABLE format a col TABLESPACE format a select i owner OWNER i index_name INDEX t table_name TABLE i tablespace_name TABLESPACE from dba_indexes i dba_tables t where i owner=t owner and i table_name=t table_name and i tablespace_name=t tablespace_name and i owner not in ( SYS SYSTEM ) /
第三 查看数据表空间里有哪些索引
用户的默认表空间应该不是SYSTEM表空间 而是数据表空间 在建立索引时 如果不指定相应的索引表空间名 那么 该索引就会建立在数据表空间中 这是程序员经常忽略的一个问题 应该在建索引时 明确的指明相应的索引表空间
col segment_name format a select owner segment_name sum(bytes) from dba_segments where tablespace_name= 数据表空间名 and segment_type= INDEX group by owner segment_name/
第四 查看哪个索引被扩展了超过 次
随着表记录的增加 相应的索引也要增加 如果一个索引的next extent值设置不合理（太小） 索引段的扩展变得很频繁 索引的extent太多 检索时的速度和效率就会降低
set linesize col owner format a col segment_name format a col tablespace_name format a select count(*) owner segment_name tablespace_name from dba_extents where segment_type= INDEX and owner not in ( SYS SYSTEM ) group by owner segment_name tablespace_name having count(*) > order by count(*) desc /
（ ）找出需要重建的索引后 需要确定索引的大小 以设置合理的索引存储参数
set linesize col INDEX format a col TABLESPACE format a select owner OWNER segment_name INDEX tablespace_name TABLESPACE bytes BYTES/COUNT sum(bytes) TOTAL BYTES round(sum(bytes)/( * ) ) TOTAL M count(bytes) TOTAL COUNT from dba_extents where segment_type= INDEX and segment_name in ( 索引名 索引名 ) group by owner segment_name segment_type tablespace_name bytes order by owner segment_name /
（ ）确定索引表空间还有足够的剩余空间
确定要把索引重建到哪个索引表空间中 要保证相应的索引表空间有足够的剩余空间
select round(bytes/( * ) ) free(M) from *** $ts_free where tablespace_name= 表空间名 /
（ ）重建索引
重建索引时要注意以下几点
a 如果不指定tablespace名 索引将建在用户的默认表空间 b 如果不指定nologging 将会写日志 导致速度变慢 由于索引的重建没有恢复的必要 所以 可以不写日志 c 如果出现资源忙 表明有进程正在使用该索引 等待一会再提交
alter index 索引名 rebuild tablespace 索引表空间名 storage(initial 初始值 next 扩展值) nologging /
（ ）检查索引
对重建好的索引进行检查
select * from dba_extents where segment_name= 索引名
（ ）根据索引进行查询 检查索引是否有效
使用相应的where条件进行查询 确保使用该索引 看看使用索引后的效果如何
select * from dba_ind_columns where index_name like 表名%
然后 根据相应的索引项进行查询
select * from 表名% where
（ ）找出有碎片的表空间 并收集其碎片
重建索引后 原有的索引被删除 这样会造成表空间的碎片
select alter tablespace ||tablespace_name|| coalesce; from dba_free_space_coalesced where percent_blocks_coalesced!= /
整理表空间的碎片
lishixinzhi/Article/program/Oracle/201311/18791alter tablespace 表空间名 coalesce