eagle’s home

案例描述如下：

表ip_postal_code_mapping中有两个列start_ip_number, end_ip_number分别表述IP地址段的起始地址和结束地址，已经将IP地址通过函数转化为number类型

比方说 192.168.1.1 = 192168001001

现在需要查看这些地址段相互之间有没有交叠的，例如192.168.1.1~192.168.1.20 和192.168.1.10~192.168.1.30即为交叠的地址段

比较自然的SQL语句写法如下：

select a.start_ip_number,a.end_ip_number,b.start_ip_number,b.end_ip_number from
ip_postal_code_mapping a,ip_postal_code_mapping b
where a.start_ip_number between b.start_ip_number and b.end_ip_number;

SQL> explain plan for select a.start_ip_number,a.end_ip_number,b.start_ip_number,b.end_ip_number from
2 ip_postal_code_mapping a,ip_postal_code_mapping b
3 where a.start_ip_number between b.start_ip_number and b.end_ip_number;

Explained.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
———————————————–
Plan hash value: 2080022078

—————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 52 | 4 (0)| 00:00:01 | | |
| 1 | NESTED LOOPS | | 1 | 52 | 4 (0)| 00:00:01 | | |
| 2 | PARTITION RANGE ALL| | 1 | 26 | 2 (0)| 00:00:01 | 1 | 6 |
| 3 | TABLE ACCESS FULL | IP_POSTAL_CODE_MAPPING | 1 | 26 | 2 (0)| 00:00:01 | 1 | 6 |
| 4 | PARTITION RANGE ALL| | 1 | 26 | 2 (0)| 00:00:01 | 1 | 6 |
|* 5 | TABLE ACCESS FULL | IP_POSTAL_CODE_MAPPING | 1 | 26 | 2 (0)| 00:00:01 | 1 | 6 |

PLAN_TABLE_OUTPUT
—————————————————————————————-

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

5 – filter(“A”.”START_IP_NUMBER”>=”B”.”START_IP_NUMBER” AND
“A”.”START_IP_NUMBER”<="B"."END_IP_NUMBER")

18 rows selected.

这个表有两百多万条记录，所以这个Nest Loop的plan非常的糟糕，而且也没有办法使用hash join，因为没有等式比较。事实证明这个SQL跑了七个小时以上还是没有跑出来。

优化的思路是这样的

1. 将表中的记录按照IP地址段的起始地址进行排序，并添加ID列标志排序的位置存入临时表中

通过排序，使得START_IP_NUMBER按照ID的递增而递增

ID START_IP_NUMBER END_IP_NUMBER
1 S1 E1
2 S2 E2
3 S3 E3
。。。

create table temp as
select rownum id, start_ip_number ,end_ip_number
from STAGING_IPMAP_USER.ip_postal_code_mapping
where version=20081217
order by start_ip_number,end_ip_number;

SQL> select count(*) from temp;

COUNT(*)
———-
2190818

2. 确认单个IP地址段的正确性 （START_IP_NUMBER必须小于等于END_IP_NUMBER）

ID START_IP_NUMBER END_IP_NUMBER
1 S1 <= E1
2 S2 <= E2
3 S3 <= E3
。。。

SQL> select * from temp where START_IP_NUMBER>END_IP_NUMBER;

no rows selected

3. 将任意记录的IP地址的结束地址和下一条记录的起始地址进行比较

如果所有记录都满足条件，则 Sn > En-1

结合Step2得到 S1<=E1

这样就说明IP地址段是没有重复的，任何违反这一条件的都是会出现有交叠的IP地址段

SQL> select a.*,b.* from temp a,temp b
2 where a.id=b.id+1 and a.START_IP_NUMBER<=b.END_IP_NUMBER;

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
——————————————————

———————————————————————————–
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
———————————————————————————–
| 0 | SELECT STATEMENT | | 109K| 4064K| | 11926 (28)| 00:00:23 |
|* 1 | HASH JOIN | | 109K| 4064K| 64M| 11926 (28)| 00:00:23 |
| 2 | TABLE ACCESS FULL| TEMP | 2190K| 39M| | 1611 (56)| 00:00:04 |
| 3 | TABLE ACCESS FULL| TEMP | 2190K| 39M| | 1611 (56)| 00:00:04 |
———————————————————————————–

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

1 – access(“A”.”ID”=”B”.”ID”+1)
filter(“A”.”START_IP_NUMBER”<="B"."END_IP_NUMBER")

16 rows selected.

这样可以通过ID列进行hash join，大大提高了速度，执行时间只需要16秒。从原来200万次FTS到现在2次FTS，速度提高100万倍。

00:19:00 SQL> select a.*,b.* from temp a,temp b
00:19:06 2 where a.id=b.id+1 and a.START_IP_NUMBER<=b.END_IP_NUMBER;

no rows selected

Elapsed: 00:00:16.06

Eagle Fan oracle