使用索引是我们面对海量数据搜索是一种常用的手段。通过有效的索引访问，可以使我们更快的访问到需要的数据，减少物理、逻辑IO，从而提高系统性能。在CBO时代，Oracle对于提交SQL的执行路径是有所选择的。一个select是走Index还是走Full Table Scan，或者别的路径，要根据Oracle对表列的统计信息收集结果加以计算出的执行计划成本而确定。在计算索引成本的公式中，索引的clustering factor是一个重要参考信息。
简单的说，clustering factor就是反映数据表行存储有序程度与索引有序程度对照的指标。如果这个值越大，说明在进行索引搜索是，需要获取的数据块数量越多，从而进行逻辑物理读的次数也就越多，相应消耗的成本Cost越高。反之，对应的成本也就越低。
原理上，可以这么理解。当我们使用索引进行查询的时候，获取到叶节点最后是符合条件的一系列ROWID，代表对应数据结果所在的物理地址。在根据ROWID所对应的位置，读取对应的数据块。如果指定的数据块（Date Block）已经存在于SGA中的Buffer Cache，就直接读取（进行逻辑读）。如果不存在于Buffer Cache，就需要先从物理存储上把数据块读取到内存SGA里，之后再进行读取。（物理读+逻辑读）。在这个过程中，我们通常倾向于减少物理读和逻辑读的次数。如果我们要获取的数据都是在同样块或者尽可能少的数据块里，那么我们索引的执行效率较高。如果有一个查询，虽然执行路径中包括了索引，但是将全表所有的数据块都读取在SGA里，其成本也是值得商榷的。
Clustering Factor就是衡量索引执行效率成本的一个重要指标。我们通常会希望数据表中排列的顺序与索引列排序的顺序一致。但是，在使用一些存储结构（随机存储结构），索引和数据分开存储，数据行一般为随机存储。这样，Clustering Factor是一个不断退化的过程。下面，我们通过实验，观察clustering factor对于数据索引的影响，以及优化的方法。
1、 构建实验环境
我们选择Oracle版本为10gR2。
//获取当前版本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0–Production
Executed in 0.04 seconds
dba_object构建一张百万级数据表，构造脚本如下：
//插入脚本
declare
i number;
begin
for i in 1..10 loop
insert /*+ append */ into t
select * from dba_objects order by i;
commit;
end loop;
end;
插入大约一百万条数据。
SQL> select count(*) from t;
COUNT(*)
----------
1008000
Executed in 4.987 seconds
2、加入数据索引，收集统计信息
我们构建的数据表基本是无序的，数据字典分析情况如下：
SQL> select owner, segment_name, blocks, extents, bytes from dba_segments where wner='SYS' and segment_name='T';
OWNER SEGMENT_NA BLOCKS EXTENTS BYTES
------- ---------- ---------- ---------- ----------
SYS T 14208 85 116391936
Executed in 0.221 seconds
构建t上的object_id索引列。
SQL> create index ind_t_id on t(object_id);
Index created
Executed in 17.515 seconds
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
Executed in 21.29 seconds
其索引的字典信息。
SQL> select owner, segment_name, segment_type,blocks, extents, bytes from dba_segments where wner='SYS' and segment_name=upper('ind_t_id');
OWNER SEGMENT_NA SEGMENT_TYPE BLOCKS EXTENTS BYTES
------- ---------- ------------------ ---------- ---------- ----------
SYS IND_T_ID INDEX 2304 33 18874368
Executed in 0.17 seconds
发现，数据表T本身占据空间超过116兆，对应的索引为18.9兆左右。
3、未进行数据表重构前，执行效率分析
在未进行重构之前，数据表T对应索引ind_t_id的clustering_factor信息如下：
SQL> select owner, index_name, clustering_factor, num_rows from dba_indexes where wner='SYS' and index_name='IND_T_ID';
OWNER INDEX_NAME CLUSTERING_FACTOR NUM_ROWS
------- --------------- ----------------- --------------------
SYS IND_T_ID 1008000 1008000
Executed in 0.14 seconds
对索引列查询，分析精确查询和范围查询两种分析。
精确查询：
SQL> select * from t where object_id=1500;
已选择20行。
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 4182247035
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1860 | 23 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 20 | 1860 | 23 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_ID | 20 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1500)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
3126 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
范围查询：
当选取范围略大的时候，Oracle执行优化器，会主动拒绝执行索引。
SQL> select * from t where object_id>1000 and object_id<2000
已选择19980行。
已用时间: 00: 00: 04.85
执行计划
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18947 | 1720K| 3092 (3)| 00:00:38 |
|* 1 | TABLE ACCESS FULL| T | 18947 | 1720K| 3092 (3)| 00:00:38|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<2000 AND "OBJECT_ID">1000)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
15179 consistent gets
13567 physical reads
1484 redo size
993274 bytes sent via SQL*Net to client
15026 bytes received via SQL*Net from client
1333 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19980 rows processed
虽然在数据列object_id上加了索引，而且查询返回的数据量不到2万行，远远少于数据表数据总量（100万）。Oracle优化器在选取路径的时候，放弃了按照索引进行搜索的方案。
同样的条件，使用Hint提示方法，强制走索引也可以查看效果。
SQL> select /*+ index(t ind_t_id) */ * from t where object_id>1000 and object_id<2000;
已选择19980行。
已用时间: 00: 00: 01.65
执行计划
----------------------------------------------------------
Plan hash value: 4182247035
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18947 | 1720K| 19089 (1)| 00:03:50 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 18947 | 1720K|19089 (1)| 00:03:50|
|* 2 | INDEX RANGE SCAN | IND_T_ID | 19020 | | 45 (3)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">1000 AND "OBJECT_ID"<2000)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
21586 consistent gets
244 physical reads
16892 redo size
1938406 bytes sent via SQL*Net to client
15026 bytes received via SQL*Net from client
1333 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19980 rows processed
对比两个结果，可以看出在这个返回结果比较小（约2%）的语句中，采用索引与全表扫描差别很大。
| 对比项目 | 全表扫描 | 强制索引 |
| cpu cost | 3092 | 19083 |
| time | 00:00:38 | 00:03:50 |
| consistent get | 15179 | 21586 |
| physical reads | 13567 | 244 |
| redo size | 1484 | 16892 |
结论：我们在object_id上加入的索引ind_t_id，其clustering factor取值为100万，与数据行相同。索引健康程度比较差。在唯一查询object_id的时候，走索引。但是进行范围查询的时候，即使结果数据量仅为2%，oracle还是放弃了索引的执行计划，选择了full table scan，说明索引健康程度有时是比加索引的技巧左右大。
4、重构数据表，优化索引质量
从索引、Clustering Factor的原理看，解决clustering Factor的核心在于数据行重排。让数据行按照索引列排序的顺序存储，效果比较好。
//重构表
SQL> create table tt as select * from t where 1=0;
Table created
Executed in 0.14 seconds
SQL> insert /*+ append */ into tt select * from t order by object_id;
1008000 rows inserted
Executed in 80.225 seconds
SQL> commit;
Commit complete
Executed in 0 seconds
SQL> truncate table t;
Table truncated
Executed in 0.421 seconds
SQL> insert /*+ append */ into t select * from tt;
1008000 rows inserted
Executed in 29.182 seconds
SQL> commit;
Commit complete
Executed in 0.03 seconds
SQL> alter index ind_t_id rebuild;
Index altered
Executed in 10.665 seconds
获取统计数据，并且查看索引的健康程度。
//统计信息
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
Executed in 19.157 seconds
SQL> select owner, index_name, clustering_factor, num_rows from dba_indexes where wner='SYS' and index_name='IND_T_ID';
OWNER INDEX_NAME CLUSTERING_FACTOR NUM_ROWS
------- --------------- ----------------- --------------------
SYS IND_T_ID 13831 1008000
Executed in 0.09 seconds
可以看到，我们按照object_id进行表重构之后，索引的clustering factor缩小为13831，只有num rows的约2%。健康程度优化。
5、重新执行搜索，查看执行计划
进行精确查询：
SQL> select * from t where object_id=1500;
已选择20行。
已用时间: 00: 00: 00.08
执行计划
----------------------------------------------------------
Plan hash value: 4182247035
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1860 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 20 | 1860 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_ID | 20 | | 3 (0)| 00:00:01 |
--------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1500)
统计信息
----------------------------------------------------------
381 recursive calls
0 db block gets
62 consistent gets
1 physical reads
116 redo size
3126 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
20 rows processed
对比重构前后情况，发现CPU成本上有很明显的变化，其中Index Range Scan的成本没有发生变化，一直为3。而Table Access By Index ROWID有较大的变化，从原有的23下降到4。这一现象说明搜索索引的成本没有变化，但是根据索引返回ROWID，查找数据表块的成本有所小，和预计情况相同。
范围查询：
SQL> select * from t where object_id>1000 and object_id<2000;
已选择19980行。
已用时间: 00: 00: 01.22
执行计划
----------------------------------------------------------
Plan hash value: 4182247035
------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18980 | 1723K| 308 (1)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 18980 | 1723K| 308 (1)|00:00:04|
|* 2 | INDEX RANGE SCAN | IND_T_ID | 19031 | | 45 (3)|00:00:01|
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">1000 AND "OBJECT_ID"<2000)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
2931 consistent gets
249 physical reads
0 redo size
1938406 bytes sent via SQL*Net to client
15026 bytes received via SQL*Net from client
1333 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19980 rows processed
从返回的结果看，进行数据表重构后，我们的查询效率大大提升，原有Oracle选择全表扫描的情况已经不再出现，直接选择了Index搜索。而Index搜索的效果也有相当提升，CPU成本只有308，消耗时间00:00:04。数据逻辑读块只有2931，物理读只有249。两次试验的详细对比如下：
| 对比项目 | 未重构表 | 重构后 | |||
| 全表扫描 （范围） | 强制索引 （范围） | 准确查询 | 索引搜索 （范围） | 准确查询 | |
| cpu cost | 3092 | 19083 | 23 | 308 | 4 |
| time | 00:00:38 | 00:03:50 | 00:00:01 | 00:00:04 | 00:00:01 |
| consistent get | 15179 | 21586 | 25 | 2931 | 62 |
| physical reads | 13567 | 244 | 0 | 249 | 1 |
| redo size | 1484 | 16892 | 0 | 0 | 116 |
6、结论
通过上面的实验，我们可以看到：clustering factor是索引健康程度的一个重要指标。我们有的时候，虽然在数据表的指定列中加入了索引，但是因为索引的健康程度不高，可能效率很差。我们经常可以听说，如果查询比例在15%或者XXX%以下的时候，我们要加索引，但是在我们的例子中，数据只有2%，但是还是成本高于FTS（Full Table Scan），不会选择索引路径。所以，经常性的维护我们的索引是很重要的。
但是，本文书写的目的绝不是让将重构表成为一个常态任务，重构表只是一个手段。在实际中，是有一些问题的。
首先，重构表的行顺序是需要选择的。一个需要我们规划的表，其上常常不止一个索引，一个索引的clustering factor高效，可能就意味着另一个索引的恶化。所以一定要慎用。
其次，在一些物理性能不是很好的环境下做，重构数据表可能是一个比较繁重的工作。
最后，clustering factor总的趋势一般都是不断恶化（选择随机存取），合理的规划，使用其他如分区等技术也是解决我们最终问题的方案。这部分可能在海量数据库中应用比较多。
所以，在关注有没有索引的同时，也要关注我们索引的健康程度。
799
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
