手机号间相减，进行分组

zhangweifeng

有一个表，某个字段存的是电话号码，假如是2万个手机号码，跟自身对比，比较次数是2万*2万。我需要的是号码中相差小于50；相差>50 <100，相差>100 <200，相差>200 <500各有多少条记录。用什么方式可以快点呢？

PS: 我写的如下：
select  a.org_code,a.m_50,b.m_100,c.m_200,d.m_500
from
(select  a.org_code,count(0) m_50
from t_csr_phone a
inner join t_csr_phone b on a.org_code=b.org_code
where  abs(a.phone_self-b.phone_self)>0 And  abs(a.phone_self-b.phone_self)<50
group by a.org_code) a
left join
(select  a.org_code,count(0) m_100
from t_csr_phone a
inner join t_csr_phone b on a.org_code=b.org_code
where abs(a.phone_self-b.phone_self)>50 And  abs(a.phone_self-b.phone_self)<100
group by a.org_code) b on a.org_code=b.org_code
left join
(select  a.org_code,count(0) m_200
from t_csr_phone a
inner join t_csr_phone b on a.org_code=b.org_code
where abs(a.phone_self-b.phone_self)>100 And  abs(a.phone_self-b.phone_self)<200
group by a.org_code) c on a.org_code=c.org_code
left join
(select  a.org_code,count(0) m_500
from t_csr_phone a
inner join t_csr_phone b on a.org_code=b.org_code
where abs(a.phone_self-b.phone_self)>200 And  abs(a.phone_self-b.phone_self)<500
group by a.org_code) d on a.org_code=d.org_code;

执行计划
----------------------------------------------------------
Plan hash value: 1822197342

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

| Id  | Operation             | Name       | Rows  | Bytes |TempSpc| Cost (%
CPU)| Time    |

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

| 0 | SELECT STATEMENT       |          | 18E| 15E|    | 166P(
100)|999:59:59 |

| 1 |  MERGE JOIN OUTER       |          | 18E| 15E|    | 166P(
100)|999:59:59 |

| 2 | SORT JOIN             |          | 36P|  2447P|  5511P| 16T(
100)|999:59:59 |

|*  3 | HASH JOIN RIGHT OUTER |          | 36P|  2447P| 18M| 631G(
100)|999:59:59 |

| 4 |    VIEW                |          | 527K| 12M|    |  3744 (
100)| 00:00:45 |

| 5 |    HASH GROUP BY    |          | 527K| 25M|    |  3744 (
100)| 00:00:45 |

|*  6 |    HASH JOIN       |          | 527K| 25M|    |  3665 (
100)| 00:00:44 |

| 7 |       TABLE ACCESS FULL | T_CSR_PHONE | 20546 | 501K|    | 19
(6)| 00:00:01 |

| 8 |       TABLE ACCESS FULL | T_CSR_PHONE | 20546 | 501K|    | 19
(6)| 00:00:01 |

|*  9 |    HASH JOIN OUTER    |          | 139G|  6483G| 18M|  2401K(
100)| 08:00:22 |

|  10 |    VIEW             |          | 527K| 12M|    |  3744 (
100)| 00:00:45 |

|  11 |    HASH GROUP BY    |          | 527K| 25M|    |  3744 (
100)| 00:00:45 |

|* 12 |       HASH JOIN       |          | 527K| 25M|    |  3665 (
100)| 00:00:44 |

|  13 |       TABLE ACCESS FULL| T_CSR_PHONE | 20546 | 501K|    | 19
(6)| 00:00:01 |

|  14 |       TABLE ACCESS FULL| T_CSR_PHONE | 20546 | 501K|    | 19
(6)| 00:00:01 |

|  15 |    VIEW             |          | 527K| 12M|    |  3744 (
100)| 00:00:45 |

|  16 |    HASH GROUP BY    |          | 527K| 25M|    |  3744 (
100)| 00:00:45 |

|* 17 |       HASH JOIN       |          | 527K| 25M|    |  3665 (
100)| 00:00:44 |

|  18 |       TABLE ACCESS FULL| T_CSR_PHONE | 20546 | 501K|    | 19
(6)| 00:00:01 |

|  19 |       TABLE ACCESS FULL| T_CSR_PHONE | 20546 | 501K|    | 19
(6)| 00:00:01 |

|* 20 | SORT JOIN             |          | 527K| 12M| 36M|  7620
(50)| 00:01:32 |

|  21 | VIEW                |          | 527K| 12M|    |  3744 (
100)| 00:00:45 |

|  22 |    HASH GROUP BY       |          | 527K| 25M|    |  3744 (
100)| 00:00:45 |

|* 23 |    HASH JOIN          |          | 527K| 25M|    |  3665 (
100)| 00:00:44 |

|  24 |    TABLE ACCESS FULL  | T_CSR_PHONE | 20546 | 501K|    | 19
(6)| 00:00:01 |

|  25 |    TABLE ACCESS FULL  | T_CSR_PHONE | 20546 | 501K|    | 19
(6)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("A"."ORG_CODE"="C"."ORG_CODE"(+))
6 - access("A"."ORG_CODE"="B"."ORG_CODE")
   filter(ABS("A"."PHONE_SELF"-"B"."PHONE_SELF")>100 AND
            ABS("A"."PHONE_SELF"-"B"."PHONE_SELF")<200)
9 - access("A"."ORG_CODE"="B"."ORG_CODE"(+))
  12 - access("A"."ORG_CODE"="B"."ORG_CODE")
   filter(ABS("A"."PHONE_SELF"-"B"."PHONE_SELF")>0 AND
            ABS("A"."PHONE_SELF"-"B"."PHONE_SELF")<50)
  17 - access("A"."ORG_CODE"="B"."ORG_CODE")
   filter(ABS("A"."PHONE_SELF"-"B"."PHONE_SELF")>50 AND
            ABS("A"."PHONE_SELF"-"B"."PHONE_SELF")<100)
  20 - access("A"."ORG_CODE"="D"."ORG_CODE"(+))
   filter("A"."ORG_CODE"="D"."ORG_CODE"(+))
  23 - access("A"."ORG_CODE"="B"."ORG_CODE")
   filter(ABS("A"."PHONE_SELF"-"B"."PHONE_SELF")>200 AND
            ABS("A"."PHONE_SELF"-"B"."PHONE_SELF")<500)

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
      25  recursive calls
      0  db block gets
   1088  consistent gets
      0  physical reads
      0  redo size
      711  bytes sent via SQL*Net to client
      385  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      10  sorts (memory)
      0  sorts (disk)
      2  rows processed

执行时间:  00: 06: 48.47

图文精华

手机号间相减，进行分组