在生产环境出现一件怪事,一个查询,按照日期生成查询,而且,查询的结果集占总数据量的比重也很小,但这个查询确不走索引扫描,见下例:
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/