我们已经知道走索引范围扫描的成本可以近似看作是和聚簇因子成正比，所以如果想让上述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 |
--------------------------------------------------------------------------
从上面显示的内容可以看出执行计划从索引范围扫描变成了全表扫描。