Oracle-表空间索引存储与碎片检查

Oracle 表空间索引存储与碎片检查 Oracle 表空间索引存储检查，Oracle 表空间索引碎片查看，包括查看系统表中的用户索引、索引的存储情况检查、索引的选择性、确定索引的实际碎片。 1、查看系统表中的用户索引 在 Oracle 中，SYSTEM 表是安装数据库时自动建立的，它包含数据库的全部数据字典Oracle 表空间索引存储检查，Oracle 表空间索引碎片查看，包括查看系统表中的用户索引、索引的存储情况检查、索引的选择性、确定索引的实际碎片。1、查看系统表中的用户索引在 Oracle 中，SYSTEM 表是安装数据库时自动建立的，它包含数据库的全部数据字典，存储过程、包、函数和触发器的定义以及系统回滚段。一般来说，应该尽量避免在 SYSTEM 表中存储非 SYSTEM 用户的对象。因为这样会带来数据库维护和管理的很多问题。一旦 SYSTEM 表损坏了，只能重新生成数据库。我们可以用下面的语句来检查在 SYSTEM 表内有没有其他用户的索引存在。以下为引用内容： SELECT *FROM dba_indexesWHERE tablespace_name = SYSTEM AND owner NOT IN (SYS, SYSTEM)2、索引的存储情况检查Oracle 为数据库中的所有数据分配逻辑结构空间。数据库空间的单位是 block 、extent 和 segment 。Block ：Oracle 使用和分配的最小存储单位。由数据库建立时设置的 DB_BLOCK_SIZE 决定的。一旦数据库生成了，数据块的大小不能改变。要想改变只能重新建立数据库。Extent ：由一组连续的 block 组成的。一个或多个 extent 组成一个 segment 。当一个 segment 中的所有空间被用完时，Oracle 为它分配一个新的 extent 。Segment ：是由一个或多个 extent 组成的。它包含某表空间中特定逻辑存储结构的所有数据。一个段中的 extent 可以是不连续的，甚至可以在不同的数据文件中。表空间(tableSpace) 段(segment) 盘区(extent) 块(block) 存储层次关系一个 object 只能对应于一个逻辑存储的 segment ，我们通过查看该 segment 中的 extent ，可以看出相应 object 的存储情况。1. 查看索引段中 extent 的数量以下为引用内容： SELECT segment_name, COUNT ( * )FROM dba_extentsWHERE segment_type = INDEX AND owner = UPPER (NEWCCS)GROUP BY segment_name2. 查看表空间内的索引的扩展情况以下为引用内容： SELECT SUBSTR (segment_name, 1, 20) SEGMENT NAME, bytes, COUNT (bytes)FROM dba_extentsWHERE segment_name IN (SELECT index_nameFROM dba_indexesWHERE tablespace_name = UPPER (NEWCCS)GROUP BY segment_name, bytesORDER BY segment_name3、索引的选择性索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有 2000 条记录，表索引列有 1980 个不同的值，那么这个索引的选择性就是 1980/2000=0.99 。一个索引的选择性越接近于1，这个索引的效率就越高。如果是使用基于 cost 的最优化，优化器不应该使用选择性不好的索引。如果是使用基于 rule 的最优化，优化器在确定执行路径时不会考虑索引的选择性（除非是唯一性索引），并且不得不手工优化查询以避免使用非选择性的索引。确定索引的选择性，可以有两种方法：手工测量和自动测量。1. 手工测量索引的选择性如果要根据一个表的两列创建两列并置索引，可以用以下方法测量索引的选择性：列的选择性=不同值的数目/行的总数 /* 越接近1越好 */以下为引用内容： select count(distinct 第一列|%|第二列)/count(*) from 表名如果我们知道其中一列索引的选择性（例如其中一列是主键），那么我们就可以知道另一列索引的选择性。手工方法的优点是在创建索引前就能评估索引的选择性。2. 自动测量索引的选择性如果分析一个表，也会自动分析所有表的索引。 为了确定一个表的确定性，就要分析表。以下为引用内容： analyze table 表名 compute 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 twhere 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 有助测量每个列的选择性，但它并不能精确地测量列的并置组合的选择性。要想测量一组列的选择性，需要采用手工方法或者根据这组列创建一个索引并重新分析表。4、确定索引的实际碎片随着数据库的使用，不可避免地对基本表进行插入，更新和删除，这样导致叶子行在索引中被删除，使该索引产生碎片。插入删除越频繁的表，索引碎片的程度也越高。碎片的产生使访问和使用该索引的 I/O 成本增加。碎片较高的索引必须重建以保持最佳性能。1. 利用验证索引命令对索引进行验证。这将有价值的索引信息填入 index_stats 表。以下为引用内容： validate index 用户名.索引名或者：以下为引用内容： analyze index index_name validate structure;index_stats 只保存最近一次分析的结果2. 查询 index_stats 表以确定索引中删除的、未填满的叶子（Leaf）行的百分比 和 height 字段。以下为引用内容： select name,height, del_lf_rows, lf_rows, round(del_lf_rows/(lf_rows+0.0000000001)*100) Frag Percent from index_stats3. 如果索引的叶子行的碎片超过10%，或者 index_stats中height =4, 可以考虑对索引进行重建。以下为引用内容： alter index 用户名.索引名 rebuild tablespace 表空间名 storage ( initial 初始值 next 扩展值) nologging如何加快建 index 索引 的时间可以一次生成扩展大于10次的索引的脚本。以下为引用内容： SELECT alter index | owner | . | segment_name | rebuild;FROM ( SELECT COUNT ( * ),owner,segment_name,t.tablespace_nameFROM dba_extents tWHERE t.segment_type = INDEXAND t.owner NOT IN (SYS, SYSTEM)GROUP BY owner, segment_name, t.ta