話單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@]