求一个sql语句优化，望大神帮助！！！

[danisyan]

select DATE_REF, CURR_INTRUSION_SCORE, count(*)
from
(SELECT MCV_NO, DATE_REF,
SUM (INTRUSION_SCORE) AS CURR_INTRUSION_SCORE
FROM
(SELECT a.MCV_NO, a.CHANNEL, a.TARGET_METHOD, DATE_REF
FROM campaign.SPK2226_20150619 a
cross join
(select *
from (
select rownum as row_num, MOD(rownum,4), to_date('01/MAY/2013','dd-mon-yyyy') + rownum -1 as DATE_REF
from all_objects
where rownum <= to_date('30/MAY/2015','dd-mon-yyyy')-to_date('01/MAY/2013','dd-mon-yyyy')+1)
where mod(row_num,5) = 0) b
WHERE CONTACT_DATE >= b.DATE_REF - 60
and CONTACT_DATE <= b.DATE_REF
) utch
INNER JOIN campaign.OPT_INTRUSION_WEIGHT_P2 oiw
ON (utch.CHANNEL = oiw.CHANNEL
AND utch.TARGET_METHOD = oiw.TARGET_METHOD)
GROUP BY MCV_NO, DATE_REF)
group by DATE_REF, CURR_INTRUSION_SCORE
order by DATE_REF, CURR_INTRUSION_SCORE

I've indexed every column except the customer number. There's no Primary key because table has dupes in it (is expected). But I still get very long run times as follows:
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56M| 1015M| 343K (4)| 02:03:39 |
| 1 | SORT ORDER BY | | 56M| 1015M| 343K (4)| 02:03:39 |
| 2 | HASH GROUP BY | | 56M| 1015M| 343K (4)| 02:03:39 |
| 3 | VIEW | | 56M| 1015M| 339K (2)| 02:02:07 |
| 4 | HASH GROUP BY | | 56M| 58G| 339K (2)| 02:02:07 |
|* 5 | HASH JOIN | | 56M| 58G| 337K (2)| 02:01:22 |
| 6 | TABLE ACCESS FULL | OPT_INTRUSION_WEIGHT_P2 | 234 | 10296 | 7 (0)| 00:00:01 |
| 7 | MERGE JOIN | | 56M| 55G| 337K (2)| 02:01:17 |
| 8 | SORT JOIN | | 29M| 28G| 333K (1)| 02:00:00 |
| 9 | TABLE ACCESS BY INDEX ROWID | SPK2226_20150619 | 29M| 28G| 333K (1)| 02:00:00 |
| 10 | INDEX FULL SCAN | SPK2226_20150619_01 | 29M| | 69534 (1)| 00:25:01 |
|* 11 | FILTER | | | | | |
|* 12 | SORT JOIN | | 760 | 14440 | 29 (7)| 00:00:01 |
|* 13 | VIEW | | 760 | 14440 | 28 (4)| 00:00:01 |

[碧水幽幽泉]

优化如下：

select date_ref, curr_intrusion_score, count(*)
  from (select utch.mcv_no,
               utch.date_ref,
               sum(oiw.intrusion_score) as curr_intrusion_score
          from (select a.mcv_no, a.channel, a.target_method, date_ref
                  from campaign.spk2226_20150619 a,
                      (select date_ref
                        from (select rownum as row_num,
                                     to_date('2015-05-01','yyyy-mm-dd') + rownum - 1 as date_ref
                                from dual
                             connect by rownum <= to_date('2015-05-30','yyyy-mm-dd') 
                                                - to_date('2015-05-01','yyyy-mm-dd') + 1
                              )
                        where mod(row_num, 5) = 0
                        ) b
                 where a.contact_date >= b.date_ref - 60
                   and a.contact_date <= b.date_ref
                  ) utch
         inner join campaign.opt_intrusion_weight_p2 oiw
            on utch.channel = oiw.channel 
           and utch.target_method = oiw.target_method
         group by utch.mcv_no, utch.date_ref
        )
 group by date_ref, curr_intrusion_score
 order by date_ref, curr_intrusion_score

[danisyan]

确实节省了不少时间，谢谢了。