话单sql

要求：统计一个月中每个星期都有通话记录的号码

Select count(Distinct z.mobile_number) From
(Select Distinct substr(a.CALLINGPARTYNUMBER,3,11) mobile_number From gsmplm_msoriginating a
Where Not Exists(Select b.MSISDN_H From hdu_temp b Where substr(a.CALLINGPARTYNUMBER,3,7)=b.msisdn_h)
And a.subarea_date Between to_date('2006-4-1','yyyy-mm-dd')
And to_date('2006-4-7','yyyy-mm-dd')) z,
(Select Distinct substr(a.CALLINGPARTYNUMBER,3,11) mobile_number From gsmplm_msoriginating a
Where Not Exists(Select b.MSISDN_H From hdu_temp b Where substr(a.CALLINGPARTYNUMBER,3,7)=b.msisdn_h)
And a.subarea_date Between to_date('2006-4-8','yyyy-mm-dd')
And to_date('2006-4-14','yyyy-mm-dd')) y,
(Select Distinct substr(a.CALLINGPARTYNUMBER,3,11) mobile_number From gsmplm_msoriginating a
Where Not Exists(Select b.MSISDN_H From hdu_temp b Where substr(a.CALLINGPARTYNUMBER,3,7)=b.msisdn_h)
And a.subarea_date Between to_date('2006-4-15','yyyy-mm-dd')
And to_date('2006-4-21','yyyy-mm-dd')) x,
(Select Distinct substr(a.CALLINGPARTYNUMBER,3,11) mobile_number From gsmplm_msoriginating a
Where Not Exists(Select b.MSISDN_H From hdu_temp b Where substr(a.CALLINGPARTYNUMBER,3,7)=b.msisdn_h)
And a.subarea_date Between to_date('2006-4-22','yyyy-mm-dd')
And to_date('2006-4-30','yyyy-mm-dd')) w
Where z.mobile_number=y.mobile_number
And z.mobile_number=x.mobile_number
And z.mobile_number=w.mobile_number

第一个星期即4－1到4－7记录为81920

第二个星期即4-8到4-14记录为185253

第三个星期即4－15到4－21记录为174171

最后一个星期（超过一个星期的时间22－30）记录为344175

按以上sql执行效果不佳，要1个多小时。

但是只取前三个星期的时间为10分钟

执行计划:

Execution Plan
----------------------------------------------------------

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

| Id | Operation | Name | Rows | Bytes
| Cost |

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

| 0 | SELECT STATEMENT | | 1 | 128
| 35P|

| 1 | SORT GROUP BY | | 1 | 128
| |

| 2 | HASH JOIN RIGHT ANTI | | 18E| 15
E| 35P|

| 3 | INDEX FULL SCAN | INDX_HDUAN | 156 | 1248
| 1 |

| 4 | MERGE JOIN | | 18E| 15
E| 17P|

| 5 | SORT JOIN | | 9026T| 769
P| 2733G|

| 6 | HASH JOIN RIGHT ANTI | | 9026T| 769
P| 212G|

| 7 | INDEX FULL SCAN | INDX_HDUAN | 156 | 1248
| 1 |

| 8 | HASH JOIN | | 9084T| 710
P| 106G|

| 9 | PARTITION RANGE ITERATOR | | 7126K| 163
M| 91553 |

| 10 | TABLE ACCESS FULL | GSMPLM_MSORIGINATING | 7126K| 163
M| 91553 |

| 11 | HASH JOIN RIGHT ANTI | | 127G| 7598
G| 3116K|

| 12 | INDEX FULL SCAN | INDX_HDUAN | 156 | 1248
| 1 |

| 13 | HASH JOIN | | 128G| 6691
G| 1618K|

| 14 | HASH JOIN RIGHT ANTI | | 2190K| 66
M| 27489 |

| 15 | INDEX FULL SCAN | INDX_HDUAN | 156 | 1248
| 1 |

| 16 | PARTITION RANGE ITERATOR| | 2205K| 50
M| 27462 |

| 17 | TABLE ACCESS FULL | GSMPLM_MSORIGINATING | 2205K| 50
M| 27462 |

| 18 | PARTITION RANGE ITERATOR | | 5856K| 134
M| 79071 |

| 19 | TABLE ACCESS FULL | GSMPLM_MSORIGINATING | 5856K| 134
M| 79071 |

| 20 | SORT JOIN | | 17M| 389
M| 338K|

| 21 | PARTITION RANGE ITERATOR | | 17M| 389
M| 217K|

| 22 | TABLE ACCESS FULL | GSMPLM_MSORIGINATING | 17M| 389
M| 217K|

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

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/545828/viewspace-843277/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/545828/viewspace-843277/