oracle 回表,oracle 集群因子

集群因子(CLUSTERING FACTOR)

官方解释：index clustering factor

A measure of the row order in relation to an indexed value such as last name.

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 IDX_OBJECT_ID TEST 1192

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 rownum<=5;

OBJECT_ID ROWID

2 AAAS6DAAEAAAAILAAw

3 AAAS6DAAEAAAAILAAF

4 AAAS6DAAEAAAAILAAx

5 AAAS6DAAEAAAAILAAa

6 AAAS6DAAEAAAAILAAV

ROWID:6363 对象号-文件号-块号-行号

rowid 是伪列， oracle 专用的虚拟列，每个表的每一行都有 rowid。

每一行的 rowid 是全局唯一的。(行的×××号)

rowid 18 位， 64 进制

包括：所在表的对象号 数据文件号 块号 块行号

6 位对象号， 3 位文件号， 6 位块号， 3 位行号

AAAS6D AAE AAAAIL AAw

AAAS6D AAE AAAAIL AAF

AAAS6D AAE AAAAIL AAx

AAAS6D AAE AAAAIL AAa

AAAS6D AAE AAAAIL AAV

集群因子的算法如下：

首先比较2,3 对应的ROWID是否在同一个数据块，如果在同一个数据块，Clustering Factor +0,如果不在同一个数据块，那么Clustering Factor +1。

然后比较3,4对应的ROWID是否在同一个数据块，如果在同一个数据块，Clustering Factor +0,如果不在同一个数据块，那么Clustering Factor +1。

再然后比较4,5对应的ROWID是否在同一个数据块，如果在同一个数据块，Clustering Factor +0,如果不在同一个数据块，那么Clustering Factor +1。

像上面步骤一样，一直这样有序的比较下去，直到比较完索引中最后一个键值。

根据算法我们知道集群因子介于表的块数和表行数之间。

结论：

如果集群因子接近于块数，说明表的数据基本上是有序的，而且其顺序基本与索引顺序一样。这样在索引范围或者索引全扫描的时候，

回表只需要读取少量的数据块就能完成。

如果集群因子接近表记录数，说明表的数据和索引顺序差异很大，在索引范围扫描或者索引全扫描的时候，回表会读取更多的数据块。

集群因子只会影响索引范围扫描(index range scan)以及索引全扫描(index full scan)，因为只有这两种索引扫描方式会有大量数据回表。因为只有这两种扫描方式会回表

思考？索引唯一扫描(index unique scan) 受不受 集群因子 影响？

不受，主键集群因子再大，无所谓(只返回一行数据)

下面是根据集群因子算法，人工计算集群因子的SQL脚本

select sum(case

when block#1 = block#2 and file#1 = file#2 then

0

else

1

end) CLUSTERING_FACTOR

from (select dbms_rowid.rowid_relative_fno(rowid) file#1,

lead(dbms_rowid.rowid_relative_fno(rowid), 1, null) over(order by object_id) file#2,

dbms_rowid.rowid_block_number(rowid) block#1,

lead(dbms_rowid.rowid_block_number(rowid), 1, null) over(order by object_id) block#2

from test

where object_id is not null);

SCOTT@PROD>select 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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 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 | IDX_OBJECT_ID | 970 | | 4 (0)| 00:00:01 |

Predicate Information (identified by operation id):

2 - access("OBJECT_ID"<1000)

Statistics

0 recursive calls

0 db block gets

21 consistent gets

0 physical reads

0 redo size

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 IDX_ID2 TEST2 75209

集群因子非常大，接近于表的总行数(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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 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 id):

1 - filter("OBJECT_ID"<1000)

Note

dynamic sampling used for this statement (level=2)

Statistics

24 recursive calls

0 db block gets

1177 consistent gets

1380 physical reads

0 redo size

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，数据块全在内存中访问速度是非常快的。