Rich Jesse wrote: > Hey all, > > In 10.1.0.5.0, I have two tables of IP and lat/long info of the CSV data > from http://www.maxmind.com/app/geolitecity. PK on the location table is > locid. PK on the block table is startipnum and endipnum, with a non-unique > index on locid. There is an FK in the blocks table on location.locid. > > I created the IP-to-number function from here: > > http://cbmc64.blogspot.com/2005/05/oracle-plsql-function-to-convert_30.html > > ...and called it "ip2number" and made it DETERMINISTIC. I then tried this > simple statement: > > select /*+ first_rows(1) */ aa.* > from geo_location aa, > geo_blocks b > where aa.locid = b.locid > and ip2number('192.168.1.1') between b.startipnum and b.endipnum; > Rich, It's a question of bounds. Just add the additional condition and b.startipnum >= to_number(substr('&ip_addr', 1, instr('&ip_addr'), '.') - 1)) * 16777216 and you should feel the difference. No need to create a FBI on ip2number(). And, please, remove the hint, it's ugly. -- Stephane Faroult RoughSea Ltd <http://www.roughsea.com> -- //www.freelists.org/webpage/oracle-l