-
Notifications
You must be signed in to change notification settings - Fork 0
/
experiments.sql
85 lines (76 loc) · 3.33 KB
/
experiments.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
---
--- This file holds the experiments you can run on the tables defined in ddl.sql
--- Main discussion points about the algorithms in the comments
---
--- declarations for IP adresses. if you run the sql-s in sql developer, you'll e prompted for a value, and that's fine.
var ip varchar2(15)
exec :ip := '195.228.33.5'
--- plain-vanilla solution, where we have to calculate the masekd ip for each network according to the network's significant bit setting
--- runtime: 41 sec / query
--- full table scan is performed, as expected.
select
src.*, country_blocks.*
from (
select
:ip,
to_number(regexp_substr(:ip, '\d+', 1, 1)) * 16777216 +
to_number(regexp_substr(:ip, '\d+', 1, 2)) * 65536 +
to_number(regexp_substr(:ip, '\d+', 1, 3)) * 256 +
to_number(regexp_substr(:ip, '\d+', 1, 4)) numip
from dual
) src, country_blocks
where bitand(src.numip, bitmask) = masked_network
order by significant_bits desc;
-- using ip ranges to utilize inexes. upper bound is calculated on-the fly, but at lease ora might take advantage that not all the masked_networks
-- have to be taken into consideration.
-- runtime: 41 sec / query
-- that didn't help much. this was an unexpected result
select
src.*, country_blocks.*
from (
select
:ip,
to_number(regexp_substr(:ip, '\d+', 1, 1)) * 16777216 +
to_number(regexp_substr(:ip, '\d+', 1, 2)) * 65536 +
to_number(regexp_substr(:ip, '\d+', 1, 3)) * 256 +
to_number(regexp_substr(:ip, '\d+', 1, 4)) numip
from dual
) src, country_blocks
where src.numip between masked_network and masked_network + power(2, 32-significant_bits)
order by significant_bits desc;
-- masked network field holds the lower bound (inclusive) of the ip range
-- upper_bound field holds the last good ip address (inclusive) of the ip range
-- index is used for both lower and upper bound, which is more effective
-- runtime: 31 sec
-- I'd expected more performance gain from this approach, though 25% doesn't seem bad.
-- Still, this response time is unacceptable.
select
src.*, country_blocks.*
from (
select
:ip,
to_number(regexp_substr(:ip, '\d+', 1, 1)) * 16777216 +
to_number(regexp_substr(:ip, '\d+', 1, 2)) * 65536 +
to_number(regexp_substr(:ip, '\d+', 1, 3)) * 256 +
to_number(regexp_substr(:ip, '\d+', 1, 4)) numip
from dual
) src, country_blocks
where src.numip >= masked_network and src.numip <= upper_bound
order by significant_bits desc;
-- Calculating masks. The trick is that rownum goes from 1 to 32, which needs to be taken into consideration
select
32 - rownum + 1 as network_bits,
rownum - 1 as host_bits,
power(2, 32) - 1 - (power(2, rownum - 1) - 1) as network_mask,
power(2, rownum - 1) - 1 as host_bits_mask
from dual
connect by rownum <= 32;
-- Test index usage. Run an explain plan on this feature. There shouldn't be any full table scan in the exacution plan.
select * from city_locations where geoname_id in (6317008, 3465931, 9972414, 3395503, 714659, 1855450, 3056979);
-- Retrieving data based on location. Should be optimal if you ask SQL advisor.
select v_city_locations.*, city_locations.*
from v_city_locations, city_locations
where 1=1
and v_city_locations.geoname_id = getCityGeoNameIdByLocation(59.9454, 30.5558, 50)
and v_city_locations.geoname_id = city_locations.geoname_id
;