昨天遇到一个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

ITPub博客

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

最新文章