通信匹配号码头 SQL效率问题

Question

这个问题可能大家都碰到过。
一个号码头表 TPREL_H_CODE。表中记录为 3万条
号码头          地区        号码头长度
H_CODE       AREA_CODE H_CODE_LEN
139046851  0469 9
139046852  0469 9
139046853          0469 9
1899851          0660 7
189985127          020 9一张存储号码的表 USER_MDN ，表中的数据大概在 250 万左右。
18998512787
15360211313
13184880011
13071576800
13421520129
13432747633
13411070188现在要两张表关联，获得USER_MDN 表中 手机号码的 地区标志。匹配规则为。一个手机号码 如：18998512787 ,匹配号码头 会匹配出 两条记录：
1899851          0660 7
189985127          020 9选择其中号码头长度大的，即该号码归属地区为 0660.如果匹配不到，地区则填-1.我使用的数据库 sybase。主要是考虑效率问题。看各位经验丰富的大虾们平常工作中有没有碰到此类问题。
有什么比较高效的方法分享下？

Answer 1

我考虑的第一种方法：
  SELECT                                           
         ''''||ISNULL((SELECT AREA_CODE                                            
                        FROM (SELECT AREA_CODE,                                    
                                     RANK() OVER(PARTITION BY H_CODE               
                                                     ORDER BY H_CODE_LEN DESC) SEQ 
                                FROM TPREL_H_CODE B                            
                               WHERE A.ACC_NBR LIKE B.H_CODE || '%') X             
                       WHERE X.SEQ = 1),-1)||'''' AREA_CODE                                                       
    FROM USER_MDN A; 
-- 这种方法极其低效，根本跑不出来。

Answer 2

你用的是sybase数据啊 那你还发oracle板块？
到其他数据库开发板块去问问可能更好

Answer 3

第二种方法：1.将号码头表的 H_CODE 字段，用9拼接到 手机号码的长度，11位 NEW_H_CODE_1。
  将号码头表的 H_CODE 字段，用0拼接到 手机号码的长度，11位 NEW_H_CODE_2
2.将TPREL_H_CODE表和USER_MDN 表 的号码头字段、手机号码字段，转成 int 型。
3.用  USER_MDN.ACC_NBR <= TPREL_H_CODE.H_CODE
  AND USER_MDN.ACC_NBR >= TPREL_H_CODE.H_CODE
  取这个范围内 的 H_CODE_LEN 最大的 H_CODE.
-- 这样好歹块一点，但是还是不够。
各位多多想象办法。谢谢了！

Answer 4

其实这类问题那个数据库都问题不大，我只是想知道思路。syabse板块人太少了关键是。

Answer 5

这里写错了，应该是
.用 USER_MDN.ACC_NBR <= TPREL_H_CODE.NEW_H_CODE_1
  AND USER_MDN.ACC_NBR >= TPREL_H_CODE.NEW_H_CODE_2
  取这个范围内 的 H_CODE_LEN 最大的 H_CODE.

Answer 6

--试试，不知道语法正确不，没测试SELECT ACC_NBR ,AREA_CODE FROM (
    SELECT 
    a.ACC_NBR,
    Row_Number()over(PARTITION BY a.ACC_NBR ORDER BY Length(Nvl(b.H_CODE,0)) desc) rn,
    Nvl(b.AREA_CODE,'-1') AREA_CODE 
    FROM  USER_MDN a
    left join TPREL_H_CODE b ON InStr(a.ACC_NBR,b.H_CODE)>0
)
WHERE rn=1;

Answer 7

 我在sybase下测试了，
可以，但是很慢。。
我想要比较高效的解决办法.

Answer 8

局向数据一般不是这么存储的.
一般是
branch_num_start branch_num_end  area_code.
改造下局向表呢?

Answer 9

改成上面的用between and 就可以了，不需要再用分析函数，应该会快点。

Answer 10

分两步去做呢.
    create table user_mdn_tmp as
    SELECT 
    a.ACC_NBR,
    max(b.H_CODE) H_CODE
    FROM  USER_MDN a
    left join TPREL_H_CODE b ON a.ACC_NBR like b.H_CODE||'%';
    
    select a.acc_nbr,a.h_code,b.AREA_CODE 
      from user_mdn_tmp a left join TPREL_H_CODE b
      on a.H_CODE=b.H_CODE;

Answer 11

    create table user_mdn_tmp as
    SELECT 
    a.ACC_NBR,
    max(b.H_CODE) H_CODE
    FROM  USER_MDN a
    left join TPREL_H_CODE b ON a.ACC_NBR like b.H_CODE||'%'
    group by a.ACC_NBR;
    
    select a.acc_nbr,a.h_code,b.AREA_CODE 
      from user_mdn_tmp a left join TPREL_H_CODE b
      on a.H_CODE=b.H_CODE;上面的sql忘记写group by了.

Answer 12

分两步去做呢.
  create table user_mdn_tmp as
  SELECT  
  a.ACC_NBR,
  max(b.H_CODE) H_CODE
  FROM USER_MDN a
  left join TPREL_H_CODE b ON a.ACC_NBR like b.H_CODE||'%';就是这一步，非常慢那。

Answer 13

好像 like 是不能用到索引的吧 是吗？

Answer 14

还是回到之前把.改造下局向表.用between and 是可以用上索引的
create table TPREL_H_CODE
(H_CODE_begin varchar2(11),
h_code_end varchar2(11), 
AREA_CODE varchar2(11),
H_CODE_LEN number);
create table USER_MDN
(user_num  varchar2(11));
create index idx_TPREL_H_CODE_1 on TPREL_H_CODE(H_CODE_begin,H_CODE_end);SQL> explain plan for
  2  select /*+index(b)*/* from user_mdn a,tprel_h_code b
  3  where a.user_num between b.h_code_begin and b.h_code_end;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    41 |     6
|   1 |  TABLE ACCESS BY INDEX ROWID| TPREL_H_CODE       |     1 |    34 |     4
|   2 |   NESTED LOOPS              |                    |     1 |    41 |     6
|   3 |    TABLE ACCESS FULL        | USER_MDN           |     1 |     7 |     2
|*  4 |    INDEX RANGE SCAN         | IDX_TPREL_H_CODE_1 |     1 |       |     1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"."USER_NUM"<="B"."H_CODE_END" AND "A"."USER_NUM">="B"."H_CODE_B
       filter("A"."USER_NUM">="B"."H_CODE_BEGIN" AND "A"."USER_NUM"<="B"."H_CODE
Note
-----
   - dynamic sampling used for this statement
 
20 rows selected
 
SQL> 

Answer 15

这样,oracle先从user_mdn拿出一条记录,然后跟tprel_h_code关联,关联的时候使用索引范围扫描,比之前用like的全表扫描能提高效率.

Answer 16

with a as
(
select '1899851' a, '0660' b, 7 c from dual
union all
select '189985127', '020', 9 from dual
)
select a,b,c from
(
       select a,b,c,row_number()over(order by instr(&b,a)) rn from a where instr(&b,a)>0 
)
where rn=1--result:
1899851 0660 7

Answer 17

你表里数据有问题！GSM号头表的数据都有是7位长度的呀！你那样9位长度的重复数据是错的。
我是做手机计费的！

Answer 18

查询语句中不要用between会很慢的！要用>=and<=。

Answer 19

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 
Connected as scott
 
SQL> select * from user_mdn;
 
USER_NUM
-----------
13904685100
13904685124
13904685233
13904685288
13904685322
13904685332
18998511234
18998514321
18998512711
18998512786
 
10 rows selected
 
SQL> select * from TPREL_H_CODE;
 
H_CODE      AREA_CODE   H_CODE_LEN
----------- ----------- ----------
139046851   469                  9
139046852   469                  9
139046853   469                  9
1899851     660                  7
189985127   20                   9
 
SQL> 
SQL> create or replace procedure proc_convert_branch as
  2  
  3    v_H_CODE      VARCHAR2(11);
  4    v_AREA_CODE   VARCHAR2(11);
  5    v_H_CODE_LEN  NUMBER;
  6    v_h_code_next VARCHAR2(11);
  7    v_num         number;
  8    cursor all_to_deal is
  9      select * from TPREL_H_CODE;
 10  begin
 11    open all_to_deal;
 12    loop
 13      fetch all_to_deal
 14        into v_h_code, v_area_code, v_H_CODE_LEN;
 15      exit when all_to_deal%notfound;
 16      select count(*)
 17        into v_num
 18        from TPREL_H_CODE a
 19       where a.h_code like v_h_code || '_%';
 20      if v_num = 0 then
 21        insert into TPREL_H_CODE_transform
 22          (H_CODE, H_CODE_BEGIN, H_CODE_END, AREA_CODE, H_CODE_LEN)
 23        values
 24          (v_h_code,
 25           rpad(v_h_code, 11, '0'),
 26           rpad(v_h_code, 11, '9'),
 27           v_area_code,
 28           v_h_code_len);
 29      else
 30        select min(H_CODE)
 31          into v_h_code_next
 32          from TPREL_H_CODE a
 33         where a.h_code like v_h_code || '_%';
 34        insert into TPREL_H_CODE_transform
 35          (H_CODE, H_CODE_BEGIN, H_CODE_END, AREA_CODE, H_CODE_LEN)
 36        values
 37          (v_h_code,
 38           rpad(v_h_code, 11, '0'),
 39           rpad(v_h_code_next - 1, 11, '9'),
 40           v_area_code,
 41           v_h_code_len);
 42      end if;
 43    end loop;
 44  end;
 45  /
 
Procedure created
 
SQL> truncate table TPREL_H_CODE_transform;
 
Table truncated
 
SQL> exec proc_convert_branch;
 
PL/SQL procedure successfully completed
 
SQL> commit;
 
Commit complete
 
SQL> select * from TPREL_H_CODE_transform;
 
H_CODE      H_CODE_BEGIN H_CODE_END  AREA_CODE   H_CODE_LEN
----------- ------------ ----------- ----------- ----------
139046851   13904685100  13904685199 469                  9
139046852   13904685200  13904685299 469                  9
139046853   13904685300  13904685399 469                  9
1899851     18998510000  18998512699 660                  7
189985127   18998512700  18998512799 20                   9
 
SQL> create index idx_TPREL_H_CODE_transform1 on TPREL_H_CODE_transform(h_Code_Begin,h_Code_End);
 
Index created
 
SQL> 
SQL> select b.user_num,a.area_code from TPREL_H_CODE_transform a,user_mdn b
  2   where b.user_num between a.h_code_begin and a.h_code_end
  3  ;
 
USER_NUM    AREA_CODE
----------- -----------
13904685100 469
13904685124 469
13904685233 469
13904685288 469
13904685322 469
13904685332 469
18998511234 660
18998512711 20
18998512786 20
 
9 rows selected
 
SQL> 
SQL> explain plan for
  2  select b.user_num,a.area_code from TPREL_H_CODE_transform a,user_mdn b
  3   where b.user_num between a.h_code_begin and a.h_code_end;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                   | Name                        | Rows  | Byte
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                             |     1 |    3
|   1 |  TABLE ACCESS BY INDEX ROWID| TPREL_H_CODE_TRANSFORM      |     1 |    2
|   2 |   NESTED LOOPS              |                             |     1 |    3
|   3 |    TABLE ACCESS FULL        | USER_MDN                    |     1 |
|*  4 |    INDEX RANGE SCAN         | IDX_TPREL_H_CODE_TRANSFORM1 |     1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("B"."USER_NUM"<="A"."H_CODE_END" AND "B"."USER_NUM">="A"."H_CODE_B
       filter("B"."USER_NUM">="A"."H_CODE_BEGIN" AND "B"."USER_NUM"<="A"."H_CODE
 
16 rows selected
 
SQL> 

Answer 20

create table TPREL_H_CODE_TRANSFORM
(
  H_CODE       VARCHAR2(11),
  H_CODE_BEGIN VARCHAR2(11),
  H_CODE_END   VARCHAR2(11),
  AREA_CODE    VARCHAR2(11),
  H_CODE_LEN   NUMBER
);

Answer 21

我说几点不知道对你有没有用：1.一定要建索引，按你的匹配是能用到索引的。
                            2.你可以写个存储过程来完成，思路是从号码表中取出一个号，在头表中匹配。
3.我不知道sysbase中有没有job，使用job可以提高效率。