Re: Function in WHERE performance issue

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: rjoralist@xxxxxxxxxxxxxxxxxxxxx
  • Date: Sat, 11 Jul 2009 10:36:44 +0200

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


Other related posts: