11G的sql优化问题TABLE ACCESS FULL不走索引

sainker

select to_char(tracedata0_.RECEIVE_DATE, 'yyyy-MM-dd') as col_0_0_, tracedata0_.CONTENT_TYPE as col_1_0_, count(*) as col_2_0_ from TRACE_DATA tracedata0_ where 1=1 and tracedata0_.RECEIVE_DATE>=:1 and tracedata0_.RECEIVE_DATE<:2 group by tracedata0_.CONTENT_TYPE , to_char(tracedata0_.RECEIVE_DATE, 'yyyy-MM-dd') 由于先前在另个查询语句用到CONDITION_ID,PROJECT_ID,RECEIVE_DATE，我已经做的复合索引RECEIVE_DATE IDX_COND_PROJ_RECEDATE(CONDITION_ID,PROJECT_ID,RECEIVE_DATE)，但是这个select还是为何还是走TABLE ACCESS FULL而不走索引，询问下高手

renxiao2003

由于你使用了函数。

duolanshizhe

1. 
   
2. 
   
3. select to_char(tracedata0_.RECEIVE_DATE, 'yyyy-MM-dd') as col_0_0_, tracedata0_.CONTENT_TYPE as col_1_0_, count(*) as col_2_0_
   
4. from TRACE_DATA tracedata0_
   
5. where 1=1
   
6. and tracedata0_.RECEIVE_DATE>=:1
   
7. and tracedata0_.RECEIVE_DATE<:2
   
8. group by tracedata0_.CONTENT_TYPE , to_char(tracedata0_.RECEIVE_DATE, 'yyyy-MM-dd')
   
9. 
   
10. 由于先前在另个查询语句用到CONDITION_ID,PROJECT_ID,RECEIVE_DATE，我已经做的复合索引RECEIVE_DATE IDX_COND_PROJ_RECEDATE(CONDITION_ID,PROJECT_ID,RECEIVE_DATE)，

复制代码

几个问题需要注意：
1、sql格式混乱 ，不好处理
2、复合索引 中receive_date非前导列 所以不一定能用到
3、也许成优化器在计算成本时确实是全表扫描成本小于走索引

sainker

SQL> select to_char(tracedata0_.RECEIVE_DATE, 'yyyy-MM-dd') as col_0_0_, tracedata0_.CONTENT_TYPE as col_1_0_, count(*) as col_2_0_
from TRACE_DATA tracedata0_
where 1=1 and tracedata0_.RECEIVE_DATE>=to_date('2010-12-05','yyyy-MM-dd') and tracedata0_.RECEIVE_DATE<to_date('2010-12-06','yyyy-MM-dd') group by tracedata0_.CONTENT_TYPE , to_char(tracedata0_.RECEIVE_DATE, 'yyyy-MM-dd');  2    3  

COL_0_0_
----------
COL_1_0_
--------------------------------------------------------------------------------
  COL_2_0_
----------
2010-12-05
mattersort_bbs
     10004

2010-12-05
mattersort_news
     68166

COL_0_0_
----------
COL_1_0_
--------------------------------------------------------------------------------
  COL_2_0_
----------

2010-12-05
mattersort_blog
     78996



执行计划
----------------------------------------------------------
Plan hash value: 663623993

--------------------------------------------------------------------------------
--------------------------

| Id  | Operation                     | Name                         | Rows  | Bytes
| Cost (%CPU)| Time         |

--------------------------------------------------------------------------------
--------------------------

|   0 | SELECT STATEMENT             |                                 |     3 |    72
|    68   (2)| 00:00:01 |

|   1 |  HASH GROUP BY                     |                                 |     3 |    72
|    68   (2)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| TRACE_DATA                 |    10 |   240
|    67   (0)| 00:00:01 |

|*  3 |    INDEX SKIP SCAN             | IDX_CONID_PROID_RECE_DATE |     1 |
|    66   (0)| 00:00:01 |

--------------------------------------------------------------------------------
--------------------------


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

   3 - access("TRACEDATA0_"."RECEIVE_DATE">=TO_DATE(' 2010-12-05 00:00:00', 'syy
yy-mm-dd

              hh24:mi:ss') AND "TRACEDATA0_"."RECEIVE_DATE"<TO_DATE(' 2010-12-06
00:00:00', 'syyyy-mm-dd

              hh24:mi:ss'))
       filter("TRACEDATA0_"."RECEIVE_DATE">=TO_DATE(' 2010-12-05 00:00:00', 'syy
yy-mm-dd

              hh24:mi:ss') AND "TRACEDATA0_"."RECEIVE_DATE"<TO_DATE(' 2010-12-06
00:00:00', 'syyyy-mm-dd

              hh24:mi:ss'))


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      30182  consistent gets
          0  physical reads
          0  redo size
        804  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
跟踪了改语句，发现使用了索引，但感觉性能不佳 30182  consistent gets，oem建议单独添加个RECEIVE_DATE索引，怎么优化比较好？

duolanshizhe

虽然是走了跳跃索引，但是发生的一致性gets有点多，建议增加个以receive_Date单独列的索引，然后再来观察执行计划？

sainker

后来把复合索引删除，建立了独立3个索引，因为有的语句用project_id，有的用rece_data还是单独分开测试下

duolanshizhe

恩  根据你的业务来决定啊！

sainker

谢谢，duolanshizhe 回答