MySQL 聚簇因子_Oracle 聚簇因子 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648788 - 惜分...

前几天被人问到聚簇因子被问住了，所以今天做了个试验说明这个问题

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操作数量，自然检索时间会大大降低。