请教:为何如下的SQL执行很慢?怎么样能让他快点呢?

Question

select trunc(scan_start_time,'mm') a,
round(avg((V_AvfAssReq_AvfSvcAss_CallDur+v_AvfSvcAss_AvfAssCmp_CallDur+v_AvrAssCmp_AvfRlsReq_CallDur+V_AvfRlsReq_KilPrc_CallDur+AvfSvcAss_AvfAssCmp_CallDur+AvrAssCmp_AvfRlsReq_CallDur+AvfRlsReq_KilPrc_CallDur)/3600),4) b 
from C_tpa_cnt_carr_zx
where scan_start_time>='2010-01-01'
and scan_start_time<'2010-12-31'
and ne_type=10000
and sum_level=1
group by trunc(scan_start_time,'mm')执行完要356秒!!太慢了!有没有办法让他快点呢?
十分感谢!!!

Answer 1

oracle语句执行是从右向左，尽量把能过滤掉大量数据的条件放在外层，另外用了group by 想要变快是不可能的

Answer 2

在scan_start_time,ne_type,sum_level建立联合索引

Answer 3

对你scan_start_time，ne_type，sum_level 加上index 试试呢
还有，看下执行计划怎么走的

Answer 4

2楼和3楼的:
 create index INDEX2_C_TPA_CNT_CARR_ZX on C_TPA_CNT_CARR_ZX (SCAN_START_TIME, NE_TYPE, SUM_LEVEL)
  tablespace CDMADBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
这个是你们说的联合索引吗?这个我建立了的,还是这么慢!
   3#的:
  怎么查看执行计划呢?

Answer 5

5#:
  "先把数据量小的条件 放到里层  
在试试建个索引"啥意思?
把下面这几个条件更换下先后顺序?然后再按照这个顺序建索引?
scan_start_time>='2010-01-01'
and scan_start_time<'2010-12-31'
and ne_type=10000
and sum_level=1
group by trunc(scan_start_time,'mm')

Answer 6

嗯 对的  看那几个条件 那个要过滤的数据最少   还有就是建个后面用到的字段建联合索引开启看执行计划
set autotrace on

Answer 7

EXPLAIN PLAN FOR select trunc(scan_start_time,'mm') a,
round(avg((V_AvfAssReq_AvfSvcAss_CallDur+v_AvfSvcAss_AvfAssCmp_CallDur+v_AvrAssCmp_AvfRlsReq_CallDur+V_AvfRlsReq_KilPrc_CallDur+AvfSvcAss_AvfAssCmp_CallDur+AvrAssCmp_AvfRlsReq_CallDur+AvfRlsReq_KilPrc_CallDur)/3600),4) b  
from C_tpa_cnt_carr_zx
where scan_start_time>='2010-01-01'
and scan_start_time<'2010-12-31'
and ne_type=10000
and sum_level=1
group by trunc(scan_start_time,'mm') ;SELECT * FROM TABLE(dbms_xplan.display);

Answer 8

PLAN_TABLE_OUTPUT
Plan hash value: 1042337171
 
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                       |     1 |    34 | 16152   (3)| 00:03:14 |       |       |
|   1 |  HASH GROUP BY                       |                       |     1 |    34 | 16152   (3)| 00:03:14 |       |       |
|*  2 |   FILTER                             |                       |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR          |                       |     1 |    34 | 16151   (3)| 00:03:14 |   KEY |   KEY |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| C_TPA_CNT_CARR_ZX     |     1 |    34 | 16151   (3)| 00:03:14 |   KEY |   KEY |
|*  5 |      INDEX SKIP SCAN                 | IDX_C_TPA_CNT_CARR_ZX |     1 |       | 16150   (3)| 00:03:14 |   KEY |   KEY |
------------------------------------------------------------------------------------------------------------------------------
这个是执行结果,这个能看出什么啊?