集群因子用于判断索引回表需要消耗的物理I/O 次数。

    我们先对测试表 test 的 object_id 列创建一个索引 idx_id。

    1. create index idx_id on test(object_id);
    1. Index created.

    然后我们查看该索引的集群因子。

    1. select owner, index_name, clustering_factor
    2.     from dba_indexes
    3.    where owner = 'SCOTT'
    4.      and index_name = 'IDX_ID';
    1. OWNER      INDEX_NAME CLUSTERING_FACTOR
    2. ---------- ---------- -----------------
    3. SCOTT      IDX_ID                  1094

    索引 idx_id 的叶子块中有序地存储了索引的键值以及键值对应行所在的 ROWID。

    1. 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;
    1.  OBJECT_ID ROWID
    2. ---------- ------------------
    3.          2 AAASNJAAEAAAAITAAw
    4.          3 AAASNJAAEAAAAITAAF
    5.          4 AAASNJAAEAAAAITAAx
    6.          5 AAASNJAAEAAAAITAAa
    7.          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 脚本。

    1. 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);
    1. CLUSTERING_FACTOR
    2. -----------------
    3.              1094

    我们来查看索引 idx_id 的集群因子接近表的总行数还是表的总块数。

    通过前面的章节我们知道，表的总行数为 72 462 行。

    表的总块数如下可知。

    1. select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks
    2.     from test;
    1.     BLOCKS
    2. ----------
    3.       1032

    集群因子非常接近表的总块数。现在，我们来查看下面 SQL 语句的执行计划。

    1. set arraysize 5000
    2. set autot trace
    3. select * from test where object_id < 1000;
    1. 942 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 3946039639
    7. -------------------------------------------------------------------------------------
    8. | Id | Operation                  | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    9. -------------------------------------------------------------------------------------
    10. |  0 | SELECT STATEMENT           |        |   970 | 94090 |    19   (0)| 00:00:01 |
    11. |  1 | TABLE ACCESS BY INDEX ROWID| TEST   |   970 | 94090 |    19   (0)| 00:00:01 |
    12. |* 2 | INDEX RANGE SCAN           | IDX_ID |   970 |       |     4   (0)| 00:00:01 |
    13. -------------------------------------------------------------------------------------
    15. Predicate Information (identified by operation id):
    16. ---------------------------------------------------
    18.    2 - access("OBJECT_ID"<1000)
    20. Statistics
    21. ----------------------------------------------------------
    22.           0  recursive calls
    23.           0  db block gets
    24.          17  consistent gets
    25.           0  physical reads
    26.           0  redo size
    27.       86510  bytes sent via SQL*Net to client
    28.         420  bytes received via SQL*Net from client
    29.           2  SQL*Net roundtrips to/from client
    30.           0  sorts (memory)
    31.           0  sorts (disk)
    32.         942  rows processed

    该 SQL 耗费了 17 个逻辑读。

    现在我们新建一个测试表 test2 并且对数据进行随机排序。

    1. create table test2 as select * from test order by dbms_random.value;
    1. Table created.

    我们在 object_id 列创建一个索引 idx_id2。

    1. create index idx_id2 on test2(object_id);
    1. Index created.

    我们查看索引 idx_id2 的集群因子。

    1.  select owner, index_name, clustering_factor
    2.     from dba_indexes
    3.    where owner = 'SCOTT'
    4.      and index_name = 'IDX_ID2';
    1. OWNER      INDEX_NAME CLUSTERING_FACTOR
    2. ---------- ---------- -----------------
    3. SCOTT      IDX_ID2                72393

    索引 idx_id2 的集群因子接近于表的总行数，回表的时候会读取更多的数据块，现在我们来看一下 SQL 的执行计划。

    1.  set arraysize 5000
    2.  set autot trace
    3.  select /*+ index(test2) */ * from test2 where object_id <1000;
    1. 942 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 3711990673
    7. -------------------------------------------------------------------------------------
    8. | Id | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    9. -------------------------------------------------------------------------------------
    10. |  0 | SELECT STATEMENT           |         |   942 |   190K|   855   (0)| 00:00:11 |
    11. |  1 | TABLE ACCESS BY INDEX ROWID| TEST2   |   942 |   190K|   855   (0)| 00:00:11 |
    12. |* 2 | INDEX RANGE SCAN           | IDX_ID2 |   942 |       |     4   (0)| 00:00:01 |
    13. -------------------------------------------------------------------------------------
    15. Predicate Information (identified by operation id):
    16. ---------------------------------------------------
    18.    2 - access("OBJECT_ID"<1000)
    20. Note
    21. -----
    22.    - dynamic sampling used for this statement (level=2)
    24. Statistics
    25. ----------------------------------------------------------
    26.           0  recursive calls
    27.           0  db block gets
    28.         943  consistent gets
    29.           0  physical reads
    30.           0  redo size
    31.       86510  bytes sent via SQL*Net to client
    32.         420  bytes received via SQL*Net from client
    33.           2  SQL*Net roundtrips to/from client
    34.           0  sorts (memory)
    35.           0  sorts (disk)
    36.         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 章中我们还会进一步讨论集群因子。