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

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

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

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<20;

        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<20;

        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中数据的分布。

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的数据，因为这些数据分布比较集中，扫描的数据块就要很少，索引就很可能走索引扫描。

对于聚簇因子，可以通过重建索引，重建表，或者重新组织索引来改进，但是从实现的角度来说很困难，毕竟数据的分布情况很难模拟，如果要进行问题的复现和排查还是需要掌握不少的细节,通过备份库来复现问题也是一种思路。

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

原文：http://blog.itpub.net/23718752/viewspace-1374486/