聚簇因子和执行计划的联系

在平时的工作中，可能会碰到一种很奇怪的问题，本来在生产环境中有些sql语句执行没有问题，一个很普通的查询预期走了索引扫面，但是拷贝数据到其它环境之后，就发现却走了全表扫描。
或者情况相反，本来出现问题的查询走了全表扫描，我们尝试在测试环境中浮现，但是测试环境中在相同的数据量的情况下，查询却又走了索引扫描，问题无法复现了。
出现这种情况的原因比较复杂，涉及很多的原因，其中一个很重要的原因就是聚簇因子的导致的。
聚簇因子是一个与索引相关的统计信息，它通过查看表中的数据块来进行计算得到。
对于这个问题，可能直接说理论会有些枯燥。可以通过如下的问题来进行说明。

create table t1 as select trunc(rownum/100) id ,object_name from all_objects where rownum
create table t2 as select mod(rownum,100) id ,object_name from all_objects where rownum

create index inx_t1 on t1(id);
create index inx_t2 on t2(id);

exec dbms_stats.gather_table_stats(null,'T1',CASCADE=>true);
exec dbms_stats.gather_table_stats(null,'T2',CASCADE=>true);

查看表t1的数据类似下面的格式。
SQL> select *from t1 where rownum

        ID OBJECT_NAME
---------- ------------------------------
         0 ICOL$
         0 I_USER1
         0 CON$
         0 UNDO$
         0 C_COBJ#
         0 I_OBJ#
         0 PROXY_ROLE_DATA$
         0 I_IND1
         0 I_CDEF2
         0 I_OBJ5
         0 I_PROXY_ROLE_DATA$_1
         0 FILE$
         0 UET$
         0 I_FILE#_BLOCK#
         0 I_FILE1
         0 I_CON1
         0 I_OBJ3
         0 I_TS#
         0 I_CDEF4

19 rows selected.

查看表t2的数据类似下面的格式。
SQL> select *from t2 where rownum

        ID OBJECT_NAME
---------- ------------------------------
         1 ICOL$
         2 I_USER1
         3 CON$
         4 UNDO$
         5 C_COBJ#
         6 I_OBJ#
         7 PROXY_ROLE_DATA$
         8 I_IND1
         9 I_CDEF2
        10 I_OBJ5
        11 I_PROXY_ROLE_DATA$_1
        12 FILE$
        13 UET$
        14 I_FILE#_BLOCK#
        15 I_FILE1
        16 I_CON1
        17 I_OBJ3
        18 I_TS#
        19 I_CDEF4

19 rows selected.
下面的表格能够简要的说明数据的分布。
T1中数据的分布。

T2中数据的分布。

我们来看看同样的查询对应的执行计划。

SQL>select *from t1 where id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2808986199

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   100 |  1800 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |   100 |  1800 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INX_T1 |   100 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=2)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
       4130  bytes sent via SQL*Net to client
        586  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

SQL>select *from t1 where id=2;

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |   180 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |    10 |   180 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=2)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        820  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed
可以看到一个走了索引扫描，一个走了全表扫描。这个时候我们再来看看聚簇因子。
SQL>select i.table_name,i.index_name,i.CLUSTERING_FACTOR,t.blocks,i.NUM_ROWS from user_tables t,user_indexes i where t.table_name=i.table_name and  t.table_name in ('T1','T2');
TAB INDEX_NAME           CLUSTERING_FACTOR     BLOCKS   NUM_ROWS
--- -------------------- ----------------- ---------- ----------
T1  INX_T1                               4          7        999
T2  INX_T2                             365          7        999

表t2的数据分布比较散，表的聚簇度高，接近于表中的数据，对于id=2,因为数据分布得很开，扫描的数据块就很很多，就很可能走全表扫描。而表中t1的数据聚簇度低，比如要查找id=2的数据，因为这些数据分布比较集中，扫描的数据块就要很少，索引就很可能走索引扫描。

对于聚簇因子，可以通过重建索引，重建表，或者重新组织索引来改进，但是从实现的角度来说很困难，毕竟数据的分布情况很难模拟，如果要进行问题的复现和排查还是需要掌握不少的细节,通过备份库来复现问题也是一种思路。
tom对于聚簇因子的解释如下。
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1032431852141

Note that typically only 1 index per table will be heavily clustered (if any).  It would
be extremely unlikely for 2 indexes to be very clustered.

If you want an index to be very clustered -- consider using index organized tables.  They
force the rows into a specific physical location based on their index entry.

Otherwise, a rebuild of the table is the only way to get it clustered (but you really
don't want to get into that habit for what will typically be of marginal overall
improvement).