Oracle 聚簇因子

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:Oracle 聚簇因子

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

前几天被人问到聚簇因子被问住了,所以今天做了个试验说明这个问题
1、准备试验条件

--创建表t_1
CREATE TABLE t_1
AS
SELECT 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_2
CREATE TABLE t_2
AS
SELECT * 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 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操作数量,自然检索时间会大大降低。

此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

Oracle 聚簇因子》有 1 条评论

  1. 谭龙 说:

    有点不明白:
    我这边测试建表也是T2 比T1表多两条记录,多出来的是那两条是什么数据?
    建t_2的时候,为什么特意变更了写法,但是我测过其实按t_1的建法(order by 那些不变),结果也是不变的。

    是不是数据量太小或者分布的不算散,所以两个表的索引段大小都是差不多的(上述例子中的ind_t_1和ind_t_2)。那应该怎样解释索引有时候比表还大的原因?