转自:http://www.baidu.com/p/%E5%9C%9F%E8%91%97008?from=wenku
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 emporder by empno);
3)可以在高水位以上开辟足够的新块,把一张表的数据全部存入这里,以达到降低 聚簇因子的目的,但是带来的结果也就是空间的浪费,同时因为高水位线是全表 扫描的终点,人为的拔高了水位线,容易造成全表扫的速率降低,因此需要慎重 考虑。
4)创建分区表,以减少对数据块的访问
实验:
1、准备试验条件
--创建表t_1 CREATETABLEt_1 AS SELECTROWNUM rn,a.* FROMall_objects a ORDERBYobject_name DESC; --创建t_1表关于rownum索引 CREATEINDEXind_t_1 ONt_1(rn); --创建表表t_2 CREATETABLEt_2 AS SELECT* FROM( SELECTROWNUM rn,a.* FROMall_objects a ) ORDERBYrn ASC; --创建t_2表关于rownum索引 CREATEINDEXind_t_2 ONt_2(rn); --分析两张表及其索引 EXECDBMS_STATS.gather_table_stats(USER, 'T_1'); EXECDBMS_STATS.gather_table_stats(USER, 'T_2'); EXECDBMS_STATS.gather_index_stats(USER, 'IND_T_1'); EXECDBMS_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 calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
1807 bytes sent via SQL*Net to client
357 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21 rows processed
SQL> SELECT *FROM t_2 WHERE rn BETWEEN 100 AND 120;
已选择21行。
统计信息
———————————————————-
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1807 bytes sent via SQL*Net to client
357 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21 rows processed
3、观察试验结果
通过执行统计信息观察,t_1表的查询一致读是17,而t_2表的一致读只有7,尽然t_1的一致读尽然是t_2的2倍还多,是不是有点奇怪,同样的表结构,同样的数据(t_2多两条数据)
4、分析原因
通过查询聚簇因子发现,两个表的聚簇因子差别很大,基于rn的索引在rn是顺序排列的表中,clustering_factor的值相差很大。
在表中数据有时候属于无序状态,这个时候的CLUSTERING_FACTOR比较接近NUM_ROWS,说明如果扫描整个表,每次都要根据Index来读取相应行的RowID,这个时候的IO操作很多,自然检索时间会比较长。如果数据有序的话,CLUSTERING_FACTOR比较接近BLOCKS,说明相邻的数据在一个块中,减少了IO操作数量,自然检索时间会大大降低。