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

2015-06-19 04:00:02 230 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Sql语句的优化以及Sql语句优化工具这周在做项目的时候，发现有些Sql语句写的复杂，一个复杂的Sql语句不仅性能比较低下，当涉及到重构的时候也是不容易理解和读懂的，并且一个复杂的sql语句会显著增加程序的耦合度，所以Sql语句的优化就显的很必要了。 Sql语句复杂化的原因首先列举一下Sql语句复杂化的原因: 将业务逻辑由Sql语句实现数据库表的设计不符合第三范式 Sql语句中含有大量冗余的不必要逻辑没有很好的利用视图如何优化Sql语句呢不要超过5个表以上的连接超过5张表以上的连接会大大增加sql引擎的消耗。考虑使用临阅读详情

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 |

...全文