Oracle 聚簇因子
前几天被人问到聚簇因子被问住了，所以今天做了个试验说明这个问题
1、准备试验条件
1.1创建表t_1
|
SYS @ prod >CREATE TABLE t_1 AS SELECT ROWNUM rn,a.* FROM all_objects a ORDER BY object_name DESC;
Table created. |
1.2创建t_1表关于rownum索引
|
SYS @ prod >CREATE INDEX ind_t_1 ON t_1(rn);
Index created. |
1.3创建表表t_2
|
SYS @ prod >CREATE TABLE t_2 AS SELECT * FROM ( SELECT ROWNUM rn,a.* FROM all_objects a ) ORDER BY rn ASC;
Table created. |
1.4创建t_2表关于rownum索引
|
SYS @ prod >CREATE INDEX ind_t_2 ON t_2(rn);
Index created. |
1.5分析两张表及其索引
|
SYS @ prod >EXEC DBMS_STATS.gather_table_stats(USER, 'T_1');
PL/SQL procedure successfully completed.
SYS @ prod >EXEC DBMS_STATS.gather_table_stats(USER, 'T_2');
PL/SQL procedure successfully completed.
SYS @ prod >EXEC DBMS_STATS.gather_index_stats(USER, 'IND_T_1');
PL/SQL procedure successfully completed.
SYS @ prod >EXEC DBMS_STATS.gather_index_stats(USER, 'IND_T_2');
PL/SQL procedure successfully completed.
--说明：两个表的区别就是t_2表中的rn是有序的，刚刚建立t_2表的索引一致 |
2、执行查询操作
|
SYS @ prod >set autot traceonly stat; SYS @ prod >SELECT * FROM t_1 WHERE rn BETWEEN 100 AND 120;
21 rows selected.
Statistics ---------------------------------------------------------- 402 recursive calls 0 db block gets 78 consistent gets 0 physical reads 0 redo size 3157 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 21 rows processed
SYS @ prod >SELECT * FROM t_2 WHERE rn BETWEEN 100 AND 120;
21 rows selected.
Statistics ---------------------------------------------------------- 402 recursive calls 0 db block gets 63 consistent gets 0 physical reads 0 redo size 3157 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 21 rows processed |
3、观察试验结果
|
通过执行统计信息观察，t_1表的查询一致读是78，而t_2表的一致读只有63，尽然t_1的一致读比t_2的多，是不是有点奇怪，同样的表结构，同样的数据（t_2多两条数据） |
4、分析原因
|
SYS @ prod >@idx_stat; Enter value for input_table_name: t_1 old 15: WHERE dts.table_name = UPPER ('&input_table_name') new 15: WHERE dts.table_name = UPPER ('t_1') Enter value for owner: sys old 16: AND dts.owner = UPPER ('&owner') new 16: AND dts.owner = UPPER ('sys')
AVG LEAF BLKS AVG DATA BLKS BLEV IDX_NAME LEAF_BLKS DST_KEYS PER KEY PER KEY CLUST_FACT LAST_ANALYZED TB_BLKS TB_ROWS ---- ------------------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ---------- 1 IND_T_1 110 49902 1 1 25912 15-APR-14 718 49902
SYS @ prod >@idx_stat; Enter value for input_table_name: t_2 old 15: WHERE dts.table_name = UPPER ('&input_table_name') new 15: WHERE dts.table_name = UPPER ('t_2') Enter value for owner: sys old 16: AND dts.owner = UPPER ('&owner') new 16: AND dts.owner = UPPER ('sys')
AVG LEAF BLKS AVG DATA BLKS BLEV IDX_NAME LEAF_BLKS DST_KEYS PER KEY PER KEY CLUST_FACT LAST_ANALYZED TB_BLKS TB_ROWS ---- ------------------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ---------- 1 IND_T_2 110 49904 1 1 718 15-APR-14 718 49904
以下附带截图
表T_2的CF 值 与 该表所占据的数据块 是一样的。非常好 |
通过查询聚簇因子发现，两个表的聚簇因子差别很大，基于rn的索引在rn是顺序排列的表中，clustering_factor的值相差很大。
在表中数据有时候属于无序状态，这个时候的CLUSTERING_FACTOR比较接近NUM_ROWS，说明如 果扫描整个表，每次都要根据Index来读取相应行的RowID，这个时候的IO操作很多，自然检索时间会比较长。如果数据有序的 话，CLUSTERING_FACTOR比较接近BLOCKS，说明相邻的数据在一个块中，减少了IO操作数量，自然检索时间会大大降低。
