报表优化-------根据IP地址对比起始IP和结束IP找到相应的地域（判断两个IP地址）

这个报表很慢，通过测试发现慢的原因是在关联IP地址库上表较慢，这里需要通过给定的IP地址，对比IP地址起始IP和结束IP范围找到所在区域。

通过自定义函数直接去关联或者是通过拆分对每个值去关联，这两张方式都是十几分钟，如果查询的数据量大的话直接宕机。但是不关联IP地址库的话5秒就出结果。

我最终返回的数据有将近30万的数据，IP地址库有60多万数据。最终通过两种方式处理，方式一：32秒就出数据。方式二：16秒就出数据。

原sql:

select s.ip_address ip , dp.province || dp.city || dp.county  ip_address  from data_etl.t_statistics sleft outer join dim_ip_class dp    on(  to_number(regexp_substr(s.ip_address, '[^.]+', 1, 1)) between       to_number(regexp_substr(dp.ip_start , '[^.]+', 1, 1)) and       to_number(regexp_substr(dp.ip_end, '[^.]+', 1, 1))   and to_number(regexp_substr(s.ip_address, '[^.]+', 1, 2)) between       to_number(regexp_substr(dp.ip_end, '[^.]+', 1, 2)) and       to_number(regexp_substr(dp.ip_end, '[^.]+', 1, 2))   and to_number(regexp_substr(s.ip_address, '[^.]+', 1, 3)) between       to_number(regexp_substr(dp.ip_start, '[^.]+', 1, 3)) and       to_number(regexp_substr(dp.ip_end, '[^.]+', 1, 3))   and to_number(regexp_substr(s.ip_address, '[^.]+', 1, 4)) between       to_number(regexp_substr(dp.ip_start, '[^.]+', 1, 4)) and       to_number(regexp_substr(dp.ip_end, '[^.]+', 1, 4))   );

或者

select s.ip_address ip, dp.province || dp.city || dp.county ip_address  from data_etl.t_statistics s  left outer join dim_ip_class dp    on (get_ipaddress_func(s.ip_address) between       get_ipaddress_func(dp.ipstart) and get_ipaddress_func(dp.ipend)); 

 1、函数：

create or replace function get_ipaddress_func(p_ip varchar2)  return varchar2 as  l_cnt varchar2(100);  l_ip  varchar2(100);begin l_ip :=p_ip; select listagg(subring) WITHIN GROUP(ORDER BY rn)    into  l_cnt                     from (select level rn,                 lpad(regexp_substr('' || l_ip ||                    '', '[^.]+', 1, level), 3, 0) subring            from dual          connect by level <= 4);  return l_cnt;end;/PS：还有一种补0的方式：select LPAD(SUBSTR('012.034.6.110',                   1,                   (INSTR('012.034.6.110', '.', 1, 1) - 1)),            3,            '0') || '.' || LPAD(SUBSTR('012.034.6.110',                                       INSTR('012.034.6.110', '.', 1, 1) + 1,                                       INSTR('012.034.6.110', '.', 1, 2) -                                       INSTR('012.034.6.110', '.', 1, 1) - 1),                                3,                                '0') || '.' ||       LPAD(SUBSTR('012.034.6.110',                   INSTR('012.034.6.110', '.', 1, 2) + 1,                   INSTR('012.034.6.110', '.', 1, 3) -                   INSTR('012.034.6.110', '.', 1, 2) - 1),            3,            '0') || '.' ||       LPAD(SUBSTR('012.034.6.110', INSTR('012.034.6.110', '.', 1, 3) + 1),            3,            '0')  from dualSUBSTRING---------------012.034.006.110

2、更新dim_ip_class 表

update dim_ip_class s set s.ipstart=get_ipaddress_func(ipstart),s.ipend=get_ipaddress_func(ipend);commit;update dim_ip_class s set s.ipstart=get_ipaddress_func(ipstart),s.ipend=get_ipaddress_func(ipend);commit;

3、进行不等值连接

select s.ip_address ip , dp.province || dp.city || dp.county  ip_address  from data_etl.t_statistics s  left outer join dim_ip_class dp    on (get_ipaddress_func(s.ip_address) between dp.ipstart and dp.ipend);

方式二：
创建一个函数转化为数字，由于上一个函数经过了列转行，通过测试函数F_IP_TO_NUMBER比get_ipaddress_func快2分钟：

1.创建函数

create or replace function F_IP_TO_NUMBER(p_ipaddress in varchar2)    return number deterministic as    l_result number;begin    select to_number(substr(p_ipaddress,                            1,                            instr(p_ipaddress, '.', 1, 1) - 1)) * 255 * 255 * 255 * 255 * 1000 +           to_number(substr(p_ipaddress,                            instr(p_ipaddress, '.', 1, 1) + 1,                            instr(p_ipaddress, '.', 1, 2) -                            instr(p_ipaddress, '.', 1, 1) - 1)) * 255 * 255 * 255 * 100 +           to_number(substr(p_ipaddress,                            instr(p_ipaddress, '.', 1, 2) + 1,                            instr(p_ipaddress, '.', 1, 3) -                            instr(p_ipaddress, '.', 1, 2) - 1)) * 255 * 255 * 10 +           to_number(substr(p_ipaddress,                            instr(p_ipaddress, '.', 1, 3) + 1,                            length(p_ipaddress) -                            instr(p_ipaddress, '.', 1, 3))) * 255 + 1      into l_result      from dual;    return l_result;end;/

2、通过函数更新数据

SQL> alter table dim_ip_class add ip_start_tag number;Table alteredSQL> alter table dim_ip_class add ip_end_tag number;Table alteredSQL> update dim_ip_class set ip_start_tag=F_IP_TO_NUMBER(ipstart),  2  ip_end_tag=F_IP_TO_NUMBER(ipend);  628160 rows updatedSQL> commit;Commit complete