手机号间相减，进行分组
有一个表，某个字段存的是电话号码，假如是2万个手机号码，跟自身对比，比较次数是2万*2万。我需要的是号码中相差小于50；相差>50 <100，相差>100 <200，相差>200 <500各有多少条记录。用什么方式可以快点呢？PS: 我写的如下：
selecta.org_code,a.m_50,b.m_100,c.m_200,d.m_500
from
(selecta.org_code,count(0) m_50
from t_csr_phone a
inner join t_csr_phone b on a.org_code=b.org_code
whereabs(a.phone_self-b.phone_self)>0 Andabs(a.phone_self-b.phone_self)<50
group by a.org_code) a
left join
(selecta.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 Andabs(a.phone_self-b.phone_self)<100
group by a.org_code) b on a.org_code=b.org_code
left join
(selecta.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 Andabs(a.phone_self-b.phone_self)<200
group by a.org_code) c on a.org_code=c.org_code
left join
(selecta.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 Andabs(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
统计信息
----------------------------------------------------------
25recursive calls
0db block gets
1088consistent gets
0physical reads
0redo size
711bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
10sorts (memory)
0sorts (disk)
2rows processed
执行时间:00: 06: 48.47
页:
[1]