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
?
--------------------------------------------------------------------------------------
| I