利用函数索引，优化因cluster factor过高导致不走索引一例

在生产环境出现一件怪事，一个查询，按照日期生成查询，而且，查询的结果集占总数据量的比重也很小，但这个查询确不走索引扫描，见下例：

SQL> select count(*) from ttm_temp_order t;

COUNT(*)
----------
729465

总行数为70多万条

SQL> select count(*) from ttm_temp_order t where t.order_time>=trunc(sysdate)-2;

COUNT(*)
----------
6076

查询两天内的数据只有6000多条，理应走索引扫描

SQL> exec dbms_stats.gather_table_stats(user,'TTM_TEMP_ORDER',null,null,method_opt => 'for all indexed columns size 254',cascade => true,degree => 4);

PL/SQL procedure successfully completed.

统计信息保证是最新的

SQL> set autot trace exp stat
SQL> select * from ttm_temp_order t where t.order_time>=trunc(sysdate)-2;

6076 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 256603396

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7116 | 1660K| 5386 (1)| 00:01:05 |
|* 1 | TABLE ACCESS FULL| TTM_TEMP_ORDER | 7116 | 1660K| 5386 (1)| 00:01:05 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T"."ORDER_TIME">=TRUNC(SYSDATE@!)-2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24680 consistent gets
0 physical reads
0 redo size
1214061 bytes sent via SQL*Net to client
4924 bytes received via SQL*Net from client
407 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6076 rows processed

居然走的是全表扫描。强制索引扫描，看看逻辑读

SQL> select /*+ index(t) */ * from ttm_temp_order t where t.order_time>=trunc(sysdate)-2;

6076 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 760559715

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7116 | 1660K| 6745 (1)| 00:01:21 |
| 1 | TABLE ACCESS BY INDEX ROWID| TTM_TEMP_ORDER | 7116 | 1660K| 6745 (1)| 00:01:21 |
|* 2 | INDEX RANGE SCAN | TTM_TEMP_ORDER_IDX1 | 7116 | | 20 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T"."ORDER_TIME">=TRUNC(SYSDATE@!)-2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6445 consistent gets
0 physical reads
0 redo size
1543059 bytes sent via SQL*Net to client
4924 bytes received via SQL*Net from client
407 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6076 rows processed

比全表扫描少了不少。那为什么Oracle不选择更好的索引扫描呢？这里有一个cluster factor的概念

有兴趣的朋友可以看一下官方文档：

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i82433

SQL> select t.index_name,t.num_rows,t.clustering_factor from user_indexes t where t.index_name='TTM_TEMP_ORDER_IDX1';

INDEX_NAME NUM_ROWS CLUSTERING_FACTOR
------------------------------ ---------- -----------------
TTM_TEMP_ORDER_IDX1 729467 689050

cluster factor确实不小，这就是Oracle不选择索引扫描的原因。那为什么这里的cluster factor这么高呢？原因在于这个字段存储的是详细的时间信息，精确至秒，因此，按照cluster factor的计算方法，cluster factor确实比较高。

那么有什么办法能够让Oracle走索引扫描呢？根据这个案例的特性，我们可以建立一个函数索引，将时间部分去除，只保留日期部分，这样，应该可以降低cluster factor。建立索引过程略

SQL> select * from ttm_temp_order t where to_char(t.order_time,'YYYY-MM-DD')>=to_char(sysdate-2,'YYYY-MM-DD');

6076 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2246974986

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6073 | 1417K| 388 (0)| 00:00:05 |
| 1 | TABLE ACCESS BY INDEX ROWID| TTM_TEMP_ORDER | 6073 | 1417K| 388 (0)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | TTM_TEMP_ORDER_IDX3 | 6073 | | 20 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(TO_CHAR(INTERNAL_FUNCTION("ORDER_TIME"),'YYYY-MM-DD')>=TO_CHAR(SYSDATE@!-3,'Y
YYY-MM-DD'))

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1335 consistent gets
0 physical reads
0 redo size
1212195 bytes sent via SQL*Net to client
4924 bytes received via SQL*Net from client
407 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6076 rows processed

Oracle正确的选择了索引扫描，并且，因为cluster factor的降低，连带着consistent gets也有明显的降低。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19423/viewspace-1056056/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19423/viewspace-1056056/