oracle 集群因子

yao000

　　集群因子(CLUSTERING FACTOR)
　　

官方解释：index clustering factor　　


　　
A measure of the row order in>　　
The more order that exists in row storage for this value, the lower the clustering factor.
　　
对于此值，行存储中存在的顺序越多，聚类因子越低。
　　

　　索引idx_id的叶子块中有序的存储了索引的键值以及键值对应行所在的ROWID：
　　示例：
　　create table scott.test as select * from dba_object;
　　create index scott.idx_object_id on scott.test(object_id);
　　SCOTT@PROD>select owner,index_name,table_name,clustering_factor from dba_indexes where table_name='TEST';
　　OWNER                          INDEX_NAME                     TABLE_NAME                     CLUSTERING_FACTOR

　　SCOTT                         >　　SCOTT@PROD>select count(*) from scott.test;

COUNT(*)
　　

75275　　

　　*** 索引idx_id的叶子块中有序的存储了索引的键值以及键值对应行所在的ROWID(默认情况下索引是升序的)：
　　SCOTT@PROD> select * from (
　　2   select object_id, rowid
　　3     from test
　　4    where object_id is not null
　　5    order by object_id) where rownumselect sum(case
　　2               when block#1 = block#2 and file#1 = file#2 then
　　3                0
　　4               else
　　5                1
　　6             end) CLUSTERING_FACTOR
　　7    from (select dbms_rowid.rowid_relative_fno(rowid) file#1,
　　8    lead(dbms_rowid.rowid_relative_fno(rowid), 1, null) over(order by object_id) file#2,
　　dbms_rowid.rowid_block_number(rowid) block#1,
　　10    lead(dbms_rowid.rowid_block_number(rowid), 1, null) over(order by object_id) block#2
　　11            from test
　　12           where object_id is not null);

CLUSTERING_FACTOR
　　

         1192　　

　　和上面的一样
　　表的总块数为：
　　select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from scott.test;
　　SCOTT@PROD>select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from scott.test;
　　

BLOCKS　　

　　

  1072 　　

　　集群因子非常接近表的总块儿数的
　　实验：
　　set arraysize 5000
　　set autot trace
　　select * from scott.test where object_id < 1000;
　　SCOTT@PROD>select * from scott.test where object_id < 1000;
　　997 rows selected.

Execution Plan
　　Plan hash value: 985375477

|>
|   0 | SELECT STATEMENT            |               |   970 | 94090 |    20   (0)| 00:00:01 |
　　|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |   970 | 94090 |    20   (0)| 00:00:01 |

　　|*  2 |   INDEX RANGE SCAN          |>Predicate Information (identified by operation>　　2 - access(&quot;OBJECT_ID&quot;　　92831  bytes sent via SQL*Net to client
　　523  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　997  rows processed
　　

　　解析：返回997条数据，(index range scan) 返回一条数据要回表一次，也就意味着要回表997次，但是逻辑读consistent gets(21个)
　　因为集群因子很小，所以它回表逻辑读很小。
　　实验：现在新建一个测试表test2，并且对数据随机排序：
　　

create table scott.test2 as select * from scott.test order by dbms_random.value;　　
create index scott.idx_id2 on scott.test2(object_id);
　　

　　
查看idx_id2的集群因子：
　　

　　
select owner,index_name,table_name,clustering_factor from dba_indexes where owner='SCOTT' and table_name='TEST2';
　　

　　SCOTT@PROD>select owner,index_name,table_name,clustering_factor from dba_indexes where owner='SCOTT' and table_name='TEST2';
　　OWNER                          INDEX_NAME                     TABLE_NAME                     CLUSTERING_FACTOR

　　SCOTT                         >　　集群因子非常大，接近于表的总行数(75275)
　　再来看看相同的SQL，它的执行执行计划还有逻辑读...
　　set arraysize 5000
　　set autot trace
　　select * from scott.test2 where object_id < 1000;
　　SCOTT@PROD>set arraysize 5000
　　SCOTT@PROD>set autot trace
　　SCOTT@PROD>select * from scott.test2 where object_id < 1000;
　　997 rows selected.

Execution Plan
　　Plan hash value: 300966803

|>
|   0 | SELECT STATEMENT  |       |   997 |   201K|   180   (1)| 00:00:03 |
　　|*  1 |  TABLE ACCESS FULL| TEST2 |   997 |   201K|   180   (1)| 00:00:03 |

Predicate Information (identified by operation>　　1 - filter(&quot;OBJECT_ID&quot;　　71501  bytes sent via SQL*Net to client
　　523  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　997  rows processed
　　

　　解析：同样是返回997条数据，但是它的逻辑读(consistent gets 1177个)，差不多每一行都要对应一个块儿。集群因子越大它回表要更多的逻辑读，以及物理读
　　结论：
　　通过上面实验得知，集群因子太大会严重影响索引回表的性能。
　　集群因子究竟影响的是什么性能呢？集群因子影响的是索引回表的物理I/O次数。假设索引范围扫描返回了1000行数据，
　　假设buffer cache中没有缓存表的数据块，假设这1000行数据都在同一个数据块中，那么回表需要耗费的物理I/O就只需要一个，
　　假设这1000行数据都在不同的数据块中，那么回表就需要耗费1000个物理I/O。因此，集群因子影响索引回表的物理I/O次数。
　　

  请注意，不要尝试重建索引来降低集群因子，那根本没用，因为表中的数据顺序始终没变。　　唯一能降低集群因子的办法就是根据索引列排序对表进行重建
　　(create table new_table as select * from old_table order by 索引列)，但是这在实际操作中是不可取的，因为你无法照顾到每一个索引。
　　

　　思考？
　　怎么才能避免集群因子对SQL查询性能产生影响呢？其实前文已经有了答案，集群因子只影响索引范围扫描和索引全扫描，
　　当索引范围扫描，索引全扫描不回表或者返回数据量很少的时候，不管集群因子多大，对SQL查询性能几乎没有任何影响。
　　再次强调一遍，在进行SQL优化的时候，往往会建立合适的组合索引消除回表，或者建立组合索引尽量减少回表次数。
　　如果无法避免回表，怎么做才能消除回表对SQL查询性能产生影响呢？当我们把表中所有的数据块缓存在buffer cache中，
　　这个时候不管集群因子多大，对SQL查询性能也没有多大影响，因为这时不需要物理I/O，数据块全在内存中访问速度是非常快的。