Oracle 聚簇因子概念及实验.docx

Oracle 聚簇因子Oracle中，对于同一个查询语句，有时候会很快的完成，有时候却很慢，但是表结构什么的完全一致，表中的数据也完全一致，这个具体是什么原因呢，就要从Index中的细节说起了。 在Oracle中的一个特殊的视图user_indexes中有一个特殊的列，名字是clustering_factor，这个值的内容就是如果访问表的整个表数据，会造成多少次数据库IO。A：如果这个值与块数接近，则说明表相当有序，得到了很好的组织。在这种情况下，同一个叶子块中的索引条目可能指向同一个数据块中的行。B：如果这个值与行数接近，表的次序可能就是非常随机的。在这种情况下，同一个叶子块上的索引条目不太可能指向同一个数据块上的行。可以把聚簇因子看作是通过索引读取整个表时对表执行的逻辑I/O次数。也就是说局促因子指示了表相对于索引本身的有序程度。当oracle对索引结构执行区间扫描时，如果它发现索引中的下一行与前一行在同一个数据块上，就不会再执行另一个I/O从缓冲区缓存中获得表块。它已经有了表块的一个句柄，只需要直接使用就行了。不过，如果下一行不在同一个块上，就会释放当前的这个块，而执行另一个物理I/O在缓冲区缓存存放要处理的下一个块。我们在查询索引状态的时候，通常会用到user_indexes这张表，这张表中有一列(CLUSTERING_FACTOR 聚簇因子)，这里简单的介绍下聚簇因子的意思，大家知道数据表中的数据都是无序的存在库中，当我们在对数据进行检索的时候，查找起来很是耗费资源，于是我们就需要为表创建索引，索引的作用就是把表中的数据按照一定的顺序排列保存起来，于是就出现了一个问题，有的表中的数据和索引想要排列的顺序很是相近，而另一些表中的数据和索引想要排列的顺序相距甚远，聚簇因子的作用就是用来标示这个的，聚簇因子越小，相似度越高，聚簇因子越大，相似度越低。我们知道了聚簇因子是干嘛的了，但是还不了解标示数据的相似度有何意义，我们继续讨论：oracle在存储数据的时候，并不是按照数据块的顺序挨个进行存入数据，因为前面存入的数据经常会有dml或者ddl操作，删除数据后，原先存有数据的数据块就变成了空块，oracle为了节省存储空间，当数据库再次有新数据进行插入的话，就会优先使用那些空块，只有当空块不够使用的时候，才会去高水位以上开辟新块，这种情况也就会导致，一张表中的数据，并不是存储在相邻的数据块中，于是聚簇因子变的很大，当这种情况进行逻辑读取的时候，就会增加IO的次数，影响了读取的速度。既然说聚簇因子关系着表的读取速度，那么我们能够手工控制聚簇因子的大小吗？答案是肯定的，但是事情总是有利也有弊，1)我们可以对表进行重构（alter table emp move);2)或者按照索引的顺序重建表（create table emp_bk as select * from emp order by empno);3)可以在高水位以上开辟足够的新块，把一张表的数据全部存入这里，以达到降低 聚簇因子的目的，但是带来的结果也就是空间的浪费，同时因为高水位线是全表 扫描的终点，人为的拔高了水位线，容易造成全表扫的速率降低，因此需要慎重 考虑。4）创建分区表，以减少对数据块的访问实验：1、准备试验条件-创建表t_1CREATE TABLE t_1ASSELECT ROWNUM rn,a.* FROM all_objects a ORDER BY object_name DESC;-创建t_1表关于rownum索引CREATE INDEX ind_t_1 ON t_1(rn);-创建表表t_2CREATE TABLE t_2ASSELECT * FROM (SELECT ROWNUM rn,a.* FROM all_objects a ) ORDER BY rn ASC;-创建t_2表关于rownum索引CREATE INDEX ind_t_2 ON t_2(rn);-分析两张表及其索引EXEC DBMS_STATS.gather_table_stats(USER, T_1);EXEC DBMS_STATS.gather_table_stats(USER, T_2);EXEC DBMS_STATS.gather_index_stats(USER, IND_T_1);EXEC DBMS_STATS.gather_index_stats(USER, IND_T_2);-说明：两个表的区别就是t_2表中的rn是有序的，刚刚建立t_2表的索引一致2、执行查询操作SQL set autot traceonly stat;SQL SELECT * FROM t_1 WHERE rn BETWEEN 100 AND 120;已选择21行。统计信息-0 recursive calls0 db block gets17 consistent gets0 physical reads0 redo size1807 bytes sent via SQL*Net to client357 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)21 rows processedSQL SELECT * FROM t_2 WHERE rn BETWEEN 100 AND 120;已选择21行。统计信息-0 recursive calls0 db block gets7 consistent gets0 physical reads0 redo size1807 bytes sent via SQL*Net to client357 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)21 rows processed3、观察试验结果通过执行统计信息观察，t_1表的查询一致读是17，而t_2表的一致读只有7，尽然t_1的一致读尽然是t_2的2倍还多，是不是有点奇怪，同样的表结构，同样的数据（t_2多两条数据）4、分析原因通过查询聚簇因子发现，两个表的聚簇因子差别很大，基于rn的索引在rn是顺序排列的表中，clustering_factor的值相差很大。在表中数据有时候属于无序状态，这个时候的CLUSTERING_FACTOR