1.5 集群因子（CLUSTERING FACTOR）
集群因子用于判断索引回表需要消耗的物理I/O次数。
我们先对测试表test的object_id列创建一个索引idx_id。
SQL> create index idx_id on test(object_id); Index created.
然后我们查看该索引的集群因子。
SQL> select owner, index_name, clustering_factor 2 from dba_indexes 3 where owner = 'SCOTT' 4 and index_name = 'IDX_ID'; OWNER INDEX_NAME CLUSTERING_FACTOR ---------- ---------- ----------------- SCOTT IDX_ID 1094
索引idx_id的叶子块中有序地存储了索引的键值以及键值对应行所在的ROWID。
SQL> select * from (
2 select object_id, rowid
3 from test
4 where object_id is not null
5 order by object_id) where rownum<=5;
OBJECT_ID ROWID
---------- ------------------
2 AAASNJAAEAAAAITAAw
3 AAASNJAAEAAAAITAAF
4 AAASNJAAEAAAAITAAx
5 AAASNJAAEAAAAITAAa
6 AAASNJAAEAAAAITAAV
集群因子的算法如下。
首先我们比较2、3 对应的ROWID是否在同一个数据块，如果在同一个数据块，Clustering Factor +0；如果不在同一个数据块，那么Clustering Factor值加1。
然后我们比较3、4对应的ROWID是否在同一个数据块，如果在同一个数据块，Clustering Factor值不变；如果不在同一个数据块，那么Clustering Factor值加1。
接下来我们比较4、5对应的ROWID是否在同一个数据块，如果在同一个数据块，Clustering Factor +0；如果不在同一个数据块，那么Clustering Factor值加1。
像上面步骤一样，一直这样有序地比较下去，直到比较完索引中最后一个键值。
根据算法我们知道集群因子介于表的块数和表行数之间。
如果集群因子与块数接近，说明表的数据基本上是有序的，而且其顺序基本与索引顺序一样。这样在进行索引范围或者索引全扫描的时候，回表只需要读取少量的数据块就能完成。
如果集群因子与表记录数接近，说明表的数据和索引顺序差异很大，在进行索引范围扫描或者索引全扫描的时候，回表会读取更多的数据块。
集群因子只会影响索引范围扫描（INDEX RANGE SCAN）以及索引全扫描（INDEX FULL SCAN），因为只有这两种索引扫描方式会有大量数据回表。
集群因子不会影响索引唯一扫描（INDEX UNIQUE SCAN），因为索引唯一扫描只返回一条数据。集群因子更不会影响索引快速全扫描（INDEX FAST FULL SCAN），因为索引快速全扫描不回表。
下面是根据集群因子算法人工计算集群因子的SQL脚本。
SQL> select sum(case
2 when block#1 = block#2 and file#1 = file#2 then
3 0
4 else
5 1
6 end) CLUSTERING_FACTOR
7 from (select dbms_rowid.rowid_relative_fno(rowid) file#1,
8 lead(dbms_rowid.rowid_relative_fno(rowid), 1, null) over(order by object_id) file#2,
9 dbms_rowid.rowid_block_number(rowid) block#1,
10 lead(dbms_rowid.rowid_block_number(rowid), 1, null) over(order by object_id) block#2
11 from test
12 where object_id is not null);
CLUSTERING_FACTOR
-----------------
1094
我们来查看索引idx_id的集群因子接近表的总行数还是表的总块数。
通过前面的章节我们知道，表的总行数为72 462行。
表的总块数如下可知。
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks
2 from test;
BLOCKS
----------
1032
集群因子非常接近表的总块数。现在，我们来查看下面SQL语句的执行计划。
SQL> set arraysize 5000 SQL> set autot trace SQL> select * from test where object_id < 1000; 942 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3946039639 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 970 | 94090 | 19 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 970 | 94090 | 19 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_ID | 970 | | 4 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"<1000) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 17 consistent gets 0 physical reads 0 redo size 86510 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 942 rows processed
该SQL耗费了17个逻辑读。
现在我们新建一个测试表test2并且对数据进行随机排序。
SQL> create table test2 as select * from test order by dbms_random.value;
Table created.
我们在object_id列创建一个索引idx_id2。
SQL> create index idx_id2 on test2(object_id); Index created.
我们查看索引idx_id2的集群因子。
SQL> select owner, index_name, clustering_factor
2 from dba_indexes
3 where owner = 'SCOTT'
4 and index_name = 'IDX_ID2';
OWNER INDEX_NAME CLUSTERING_FACTOR
---------- ---------- -----------------
SCOTT IDX_ID2 72393
索引idx_id2的集群因子接近于表的总行数，回表的时候会读取更多的数据块，现在我们来看一下SQL的执行计划。
SQL> set arraysize 5000 SQL> set autot trace SQL> select / *+ index(test2) */ * from test2 where object_id <1000; 942 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3711990673 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 942 | 190K| 855 (0)| 00:00:11 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 942 | 190K| 855 (0)| 00:00:11 | |* 2 | INDEX RANGE SCAN | IDX_ID2 | 942 | | 4 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"<1000) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 943 consistent gets 0 physical reads 0 redo size 86510 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 942 rows processed
通过上面实验我们得知，集群因子太大会严重影响索引回表的性能。
集群因子究竟影响的是什么性能呢？集群因子影响的是索引回表的物理I/O次数。我们假设索引范围扫描返回了1 000行数据，如果buffer cache中没有缓存表的数据块，假设这1000行数据都在同一个数据块中，那么回表需要耗费的物理I/O就只需要一个；假设这1000行数据都在不同的数据块中，那么回表就需要耗费1 000个物理I/O。因此，集群因子影响索引回表的物理I/O次数。
请注意，不要尝试重建索引来降低集群因子，这根本没用，因为表中的数据顺序始终没变。唯一能降低集群因子的办法就是根据索引列排序对表进行重建（create table new_table as select * from old_table order by 索引列），但是这在实际操作中是不可取的，因为我们无法照顾到每一个索引。
怎么才能避免集群因子对SQL查询性能产生影响呢？其实前文已经有了答案，集群因子只影响索引范围扫描和索引全扫描。当索引范围扫描，索引全扫描不回表或者返回数据量很少的时候，不管集群因子多大，对SQL查询性能几乎没有任何影响。
再次强调一遍，在进行SQL优化的时候，往往会建立合适的组合索引消除回表，或者建立组合索引尽量减少回表次数。
如果无法避免回表，怎么做才能消除回表对SQL查询性能产生影响呢？当我们把表中所有的数据块缓存在buffer cache中，这个时候不管集群因子多大，对SQL查询性能也没有多大影响，因为这时不需要物理I/O，数据块全在内存中访问速度是非常快的。
在本书第6章中我们还会进一步讨论集群因子。