Clustering Factor过高导致查询没走索引

昨天遇到一个sql，查询一直没走索引，而走的全表扫描，检查是因为群集因子太高导致不走索引，表的行数（NUM_ROWS=135,352,160），（Clustering Factor=110,160,620）

解决方法：把索引相对的列进行排序存储，减少群集因子

重建索引是不能改变群集因子大小的

集群因子反映了数据相对于索引的列是否有序

集群因子的计算： 
(1) 按顺序扫描索引。 
(2) 将当前索引值指向的ROWID的块部分与以前的索引值进行比较 
(3) 如果ROWID指向不同的TABLE块，则增加集群因子

模拟环境：
SQL> conn scott/tiger
Connected.
SQL> create table t1 as select * from emp;
SQL> select count(*) from t1;
  COUNT(*)
----------
   1835008
SQL> create index ind_t1_empno on t1(empno);  
SQL> analyze table t1 compute statistics;  --分析表
SQL> analyze index ind_t1_empno compute statistics; --分析索引
SQL> select index_name,CLUSTERING_FACTOR from user_indexes where index_name='IND_T1_EMPNO';  查看群集因子
INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
IND_T1_EMPNO                              150850

SQL> set autot trace
SQL> select * from t1 where empno=7900;
131072 rows selected.
Elapsed: 00:00:02.04
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   131K|  4096K|  2501   (3)| 00:00:31 |
|*  1 |  TABLE ACCESS FULL| T1   |   131K|  4096K|  2501   (3)| 00:00:31 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("EMPNO"=7900)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      19587  consistent gets
       10878  physical reads
          0  redo size
    1765934  bytes sent via SQL*Net to client
      96518  bytes received via SQL*Net from client
       8740  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     131072  rows processed
以上查询走了全表扫描，而没有走索引，因为群集因子太高
SQL> alter index ind_t1_empno rebuild; --重建索引
Index altered.
SQL> analyze index ind_t1_empno compute statistics;
Index analyzed.
SQL> select index_name,CLUSTERING_FACTOR from user_indexes where index_name='IND_T1_EMPNO';
INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------

IND_T1_EMPNO                              150850

重建完索引，但是群集因子并没有减少
SQL> select * from t1 where empno=7900;
131072 rows selected.
Elapsed: 00:00:06.04
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   131K|  4096K|  2501   (3)| 00:00:31 |
|*  1 |  TABLE ACCESS FULL| T1   |   131K|  4096K|  2501   (3)| 00:00:31 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("EMPNO"=7900)

Statistics
----------------------------------------------------------
       1061  recursive calls
          0  db block gets
      19743  consistent gets
      10900  physical reads
          0  redo size
    1765934  bytes sent via SQL*Net to client
      96518  bytes received via SQL*Net from client
       8740  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
     131072  rows processed

还是走全表扫描

SQL> create table t2 as select * from t1 order by empno; 
Table created.
SQL> create index ind_t2_empno on t2(empno);
Index reated.
SQL> analyze table t2 compute statistics;
Table analyzed.
SQL> analyze index ind_t2_empno compute statistics;
Index analyzed.
SQL> select index_name,CLUSTERING_FACTOR from user_indexes where index_name='IND_T2_EMPNO';
INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
IND_T2_EMPNO                               10820
数据有序存储，减少了群集因子值
SQL> conn / as sysdba
Connected.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool; 
System altered.
SQL> conn scott/tiger
Connected.
SQL> set autot trace
SQL> select * from t2 where empno=7900;
131072 rows selected.
Elapsed: 00:00:01.88
Execution Plan
----------------------------------------------------------
Plan hash value: 632553325
--------------------------------------------------------------------------------
------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
| Time     |


--------------------------------------------------------------------------------
------------
|   0 | SELECT STATEMENT            |              |   131K|  4096K|  1058   (1)
| 00:00:13 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2           |   131K|  4096K|  1058   (1)
| 00:00:13 |
|*  2 |   INDEX RANGE SCAN          | IND_T2_EMPNO |   131K|       |   279   (2)
| 00:00:04 |

--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=7900)
Statistics
----------------------------------------------------------
       1061  recursive calls
          0  db block gets
      18605  consistent gets
       1006  physical reads
          0  redo size
    1765934  bytes sent via SQL*Net to client
      96518  bytes received via SQL*Net from client
       8740  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
     131072  rows processed