Oracle索引聚簇因子的含义及重要性

186次阅读

共计 11207 个字符，预计需要花费 29 分钟才能阅读完成。

索引统计信息中需要我们最为重点关注的是 CLUSTERING_FACTOR(聚簇因子)。

在 Oracle 数据库中，聚簇因子是指按照索引键值排序的索引行和存储于对应表中数据行的存储顺序和相似度。Oracle 是按照如下的算法来计算聚簇因子的值：

1. 聚簇因子的初始值为 1。

2.Oracle 首先定位到目标索引处于最左边的叶子块。

3. 从最左边的叶子块的第一个索引键值所在的索引行开始顺序扫描，在顺序扫描的过程中，Oracle 会比对当前索引行的 rowid 和它之前的那个索引行 (它们是相邻的关系) 的 rowid，如果这两个 rowid 并不是指向同一个表块，那么 Oracle 就将聚簇因子的当前值递增 1；如果这两个 rowid 是指向同一个表块，Oracle 就不改变聚簇因子的当前值。注意，这里 Oracle 在比对 rowid 时不需要回表去访问相应的表块。

4. 上述比对 rowid 的过程会一直持续下去，直到顺序扫描完目标索引所有叶子块里的所有索引行。

5. 上述顺序扫描操作完成后，聚簇因子的当前值就是索引统计信息中的 CLUSTERING_FACTOR，Oracle 会将其存储在数据字典里。

从上述聚簇因子的算法可以知道，如果聚簇因子的值接近对应表的表块的数量，则说明目标索引索引行和存储于对应表中数据行的存储顺序相似程度非常高。这也就意味着 Oracle 走索引范围扫描后取得目标 rowid 再回表去访问对应表块的数据时，相邻的索引行所对应的 rowid 极有可能处于同一个表块中，即 Oracle 在通过索引行记录的 rowid 回表第一次读取对应的表块并将该表块缓存在 buffer cache 中后，当再通过相邻索引行记录的 rowid 回表第二次读取对应的表块时，就不需要再产生物理 I / O 了，因为这次要访问的和上次已经访问过的表块是同一个块，Oracle 已经将其缓存在了 buffer cache 中。而如果聚簇因子的值接近对应表的记录数，则说明目标索引索引行和存储于对应表中数据行的存储顺序和相似程度非常低，这也就意味着 Oracle 走索引范围扫描取得目标 rowid 再回表去访问对应表块的数据时，相邻的索引行所对应的 rowid 极有可能不处于同一个表块中，即 Oracle 在通过索引行记录的 rowid 回表第一次去读取对应的表块并将表块缓存在 buffer cache 中后，当再通过相邻索引行记录的 rowid 回表第二次读取对应的表块时，还需要再产生物理 I /O，因为这次要访问的和上次已经访问过的表块并不是同一个块。

换句话说，聚簇因子高的索引走索引范围扫描时比相同条件下聚簇因子低的索引要耗费更多的物理 I /O，所以聚簇因子高的索引走索引范围扫描的成本会比相同条件下聚簇因子低的索引走索引范围扫描的成本高。

这里构造一个非常极端的例子，全索引中没有任何相邻的索引行记录的 rowid 指向表中相同的数据块:

Oracle 索引聚簇因子的含义及重要性

根据上述聚簇因子的算法，我们可以算出此索引 IDX_T1 的聚簇因子的值应是 20。

linuxidc@MYDB>create table t1 (id number,name char(1200));

Table created.

linuxidc@MYDB>insert into t1 values(1,’1′);

1 row created.

linuxidc@MYDB>insert into t1 values(3,’3′);

1 row created.

linuxidc@MYDB>insert into t1 values(5,’5′);

1 row created.

linuxidc@MYDB>insert into t1 values(7,’7′);

1 row created.

linuxidc@MYDB>insert into t1 values(9,’9′);

1 row created.

linuxidc@MYDB>insert into t1 values(11,’11’);

1 row created.

linuxidc@MYDB>insert into t1 values(13,’13’);

1 row created.

linuxidc@MYDB>insert into t1 values(15,’15’);

1 row created.

linuxidc@MYDB>insert into t1 values(17,’17’);

1 row created.

linuxidc@MYDB>insert into t1 values(19,’19’);

1 row created.

linuxidc@MYDB>insert into t1 values(2,’2′);

1 row created.

linuxidc@MYDB>insert into t1 values(4,’4′);

1 row created.

linuxidc@MYDB>insert into t1 values(6,’6′);

1 row created.

linuxidc@MYDB>insert into t1 values(8,’8′);

1 row created.

linuxidc@MYDB>insert into t1 values(10,’10’);

1 row created.

linuxidc@MYDB>insert into t1 values(12,’12’);

1 row created.

linuxidc@MYDB>insert into t1 values(14,’14’);

1 row created.

linuxidc@MYDB>insert into t1 values(16,’16’);

1 row created.

linuxidc@MYDB>insert into t1 values(18,’18’);

1 row created.

linuxidc@MYDB>insert into t1 values(20,’20’);

1 row created.

linuxidc@MYDB>commit;

Commit complete.

linuxidc@MYDB>create index idx_t1 on t1(id);

Index created.

linuxidc@MYDB>col location for a10
linuxidc@MYDB>select id,dbms_rowid.rowid_relative_fno(rowid)||’_’||dbms_rowid.rowid_block_number(rowid) location from t1 order by location,id;

ID LOCATION
———- ———-
1 4_300
3 4_300
5 4_300
7 4_300
9 4_300
11 4_301
13 4_301
15 4_301
17 4_301
19 4_301
2 4_302
4 4_302
6 4_302
8 4_302
10 4_302
12 4_303
14 4_303
16 4_303
18 4_303
20 4_303

20 rows selected.

从上述显示结果可以看出 1、3、5、7、9 在 4 号文件的 300 号数据块内，11、13、15、17、19 在 4 号文件的 301 号数据块内，2、4、6、8、10 在 4 号文件的第 302 号数据块内，12、14、16、18、20 在 4 号文件的第 303 号数据块内。

收集统计信息并查看聚簇因子的值

# 收集统计信息并查看聚簇因子的值
linuxidc@MYDB>exec dbms_stats.gather_table_stats(ownname=>’ZX’,tabname=>’T1′,method_opt=>’for all columns size auto’,cascade=>true,estimate_percent=>100);

PL/SQL procedure successfully completed.

linuxidc@MYDB>select index_name,clustering_factor from dba_indexes where index_name=’IDX_T1′;

INDEX_NAME CLUSTERING_FACTOR
—————————————————————————————— —————–
IDX_T1 20

在 Oracle 数据库中，能够降低目标索引的聚簇因子的唯一方法就是对表中的数据按照目标索引的索引键值排序后重新存储。需要注意的是，这种按某一个目标索引的索引键值排序后重新存储表中数据的方法确实可以降低该目标索引聚簇因子的值，但可能会同时增加该表上存在的其他索引值的聚簇因子的值。

将表 T1 的数据原封不动的照搬到表 T2 中，只不过表 T2 的数据在存储时已经按 id 列排好序了

linuxidc@MYDB>create table t2 as select * from t1 order by id;

Table created.

linuxidc@MYDB>create index idx_t2 on t2(id);

Index created.

linuxidc@MYDB>select id,dbms_rowid.rowid_relative_fno(rowid)||’_’||dbms_rowid.rowid_block_number(rowid) location from t2 order by location,id;

ID LOCATION
———- ———-
1 4_171
2 4_171
3 4_171
4 4_171
5 4_171
6 4_172
7 4_172
8 4_172
9 4_172
10 4_172
11 4_173
12 4_173
13 4_173
14 4_173
15 4_173
16 4_174
17 4_174
18 4_174
19 4_174
20 4_174

20 rows selected.

linuxidc@MYDB>exec dbms_stats.gather_table_stats(ownname=>’ZX’,tabname=>’T1′,method_opt=>’for all columns size auto’,cascade=>true,estimate_percent=>100);

PL/SQL procedure successfully completed.

linuxidc@MYDB>select index_name,clustering_factor from dba_indexes where index_name=’IDX_T2′;

INDEX_NAME CLUSTERING_FACTOR
—————————————————————————————— —————–
IDX_T2 4

重复与表 T1 相同的一系列的操作，从结果可以看出索引 IDX_T2 的聚簇因子降为了 4。而相邻的数据也都在同一数据块中。

在 Oracle 数据库里，CBO 在计算索引范围扫描 (Index Range Scan) 的成本时会使用如下公式：

IRS Cost = I/O Cost + CPU Cost

而 I /O Cost 的计算公式为：

I/O Cost = Index Access I/O Cost + Table Access I/O Cost

Index Access I/O Cost = BLEVEL + CEIL(#LEAF_BLOCKS * IX_SEL)

Table Access I/O Cost = CEIL(CLUSTERING_FACTOR * IX_SEL_WITH_FILTERS)

从这个公式可以推断走索引范围扫描的成本可以近似看作是和聚簇因子成正比，因此，聚簇因子值的大小实际上对 CBO 判断是否走相关的索引起着至关重要的作用。

演示一个例子，通过修改聚簇索引的值就让原本走索引范围扫描的执行计划变成了走全表扫描：

linuxidc@MYDB>create table t1 as select * from dba_objects;

Table created.

linuxidc@MYDB>create index idx_t1 on t1(object_id);

Index created.

linuxidc@MYDB>select clustering_factor from dba_indexes where index_name=’IDX_T1′;

CLUSTERING_FACTOR
—————–
1063

linuxidc@MYDB>select /*+ cluster_factor_expmple_1 */ object_id,object_name from t1 where object_id between 103 and 108;

OBJECT_ID OBJECT_NAME
———- ——————————
103 MIGRATE$
104 DEPENDENCY$
105 ACCESS$
106 I_DEPENDENCY1
107 I_DEPENDENCY2
108 I_ACCESS1

6 rows selected.

linuxidc@MYDB>select * from table(dbms_xplan.display_cursor(null,null,’all’));

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————
SQL_ID ga3jv3kwwwmx5, child number 0
————————————-
select /*+ cluster_factor_expmple_1 */ object_id,object_name from t1
where object_id between 103 and 108

Plan hash value: 50753647

————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————–
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 6 | 474 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 6 | | 2 (0)| 00:00:01 |
————————————————————————————–
…… 省略部分输出

SQL 走了索引范围扫描，成本值为 3

使用 Hint 强制 SQL 走全表扫描：

linuxidc@MYDB>select /*+ full(t1) */ object_id,object_name from t1 where object_id between 103 and 108;

OBJECT_ID OBJECT_NAME
———- ——————————
103 MIGRATE$
104 DEPENDENCY$
105 ACCESS$
106 I_DEPENDENCY1
107 I_DEPENDENCY2
108 I_ACCESS1

6 rows selected.

linuxidc@MYDB>select * from table(dbms_xplan.display_cursor(null,null,’all’));

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————
SQL_ID b7hjwuvmg2ncy, child number 0
————————————-
select /*+ full(t1) */ object_id,object_name from t1 where object_id
between 103 and 108

Plan hash value: 3617692013

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | | | 287 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 6 | 474 | 287 (1)| 00:00:04 |
————————————————————————–
…… 省略部分输出

现在 SQL 走全表扫描，成本值为 287。

我们已经知道走索引范围扫描的成本可以近似看作是和聚簇因子成正比，所以如果想让上述 SQL 的执行计划从索引范围扫描变为全表扫描，那么只需要调整聚簇因子的值，使走索引范围扫描的成本值大于走全表扫描的成本值 346 即可达到目的。

先将索引 IDX_T1 的聚簇因子的值手工调整为 100 万：

linuxidc@MYDB>exec dbms_stats.set_index_stats(ownname=>’ZX’,indname=>’IDX_T1′,clstfct=>1000000,no_invalidate=>false);

PL/SQL procedure successfully completed.

linuxidc@MYDB>select clustering_factor from dba_indexes where index_name=’IDX_T1′;

CLUSTERING_FACTOR
—————–
1000000

linuxidc@MYDB>select /*+ cluster_factor_expmple_2 */ object_id,object_name from t1 where object_id between 103 and 108;

OBJECT_ID OBJECT_NAME
———- ——————————
103 MIGRATE$
104 DEPENDENCY$
105 ACCESS$
106 I_DEPENDENCY1
107 I_DEPENDENCY2
108 I_ACCESS1

6 rows selected.

linuxidc@MYDB>select * from table(dbms_xplan.display_cursor(null,null,’all’));

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————
SQL_ID 1ucqsj4j0j432, child number 0
————————————-
select /*+ cluster_factor_expmple_2 */ object_id,object_name from t1
where object_id between 103 and 108

Plan hash value: 50753647

————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————–
| 0 | SELECT STATEMENT | | | | 105 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 6 | 474 | 105 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 6 | | 2 (0)| 00:00:01 |
————————————————————————————–
…… 省略部分输出

从执行计划中可以看出，成本已经由 3 变为 105(即增加了 102)，这说明我们对索引 IDX_T1 的聚簇因子的调整生效了。

要使成本值大于 287，只需要把聚簇因子的值调整到 400 万。

linuxidc@MYDB>exec dbms_stats.set_index_stats(ownname=>’ZX’,indname=>’IDX_T1′,clstfct=>4000000,no_invalidate=>false);

PL/SQL procedure successfully completed.

linuxidc@MYDB>select clustering_factor from dba_indexes where index_name=’IDX_T1′;

CLUSTERING_FACTOR
—————–
4000000

linuxidc@MYDB>select /*+ cluster_factor_expmple_3 */ object_id,object_name from t1 where object_id between 103 and 108;

OBJECT_ID OBJECT_NAME
———- ——————————
103 MIGRATE$
104 DEPENDENCY$
105 ACCESS$
106 I_DEPENDENCY1
107 I_DEPENDENCY2
108 I_ACCESS1

6 rows selected.

linuxidc@MYDB>select * from table(dbms_xplan.display_cursor(null,null,’all’));

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————
SQL_ID cwkc8q61bypa6, child number 0
————————————-
select /*+ cluster_factor_expmple_3 */ object_id,object_name from t1
where object_id between 103 and 108

Plan hash value: 3617692013

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | | | 287 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 6 | 474 | 287 (1)| 00:00:04 |
————————————————————————–