Re: Function in WHERE performance issue

  • From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
  • To: "Oracle L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 13 Jul 2009 09:28:47 -0500 (CDT)

Hey Adam,

> I've played around with the data from that source as well.
> Thankfully, I've got the IP address I'm looking up in integer format.
> I denormalized the relationship between GEO_IP_LOC and GEO_IP_ORG to
> make my own life simpler and put the data into an IOT by (ending_ip,
> starting_ip).
> I've gotten the best performance out of that data with a query like
> the following:
> Select columns from geo_ip_org_loc
>  where :integer_ip between ip_num_eng and ip_num_start
>     and ip_num_end = (select /*+ no_unnest */
>                                           min(ip_num_end)
>                                     from geo_ip_org_loc
>                                   where ip_num_end >= :integer_ip)
> Using a completely random IP address (google -
> without subselect clause: 928 recursive calls, 96768 consistent gets
> with subselect clause unhinted:  439 recursive calls, 111 consistent gets
> with subselect clause and no_unnest hint:  0 recursive calls, 7 consistent
> gets
> (We had to add the hint as part of the 9i -> 10g upgrade)
> The real problem with the data of this nature is that there's no good
> way I've found to tell Oracle that each range of (start_ip, end_ip) is
> discrete.  However, since you do know the data's discrete, the
> subselect ensures you'll only get a single matching row, shortstopping
> the range scan.

I feel a dim 10-watt bulb glowing faintly over my head!  This explains
Jonathan's post.

As I was replying to Stephane, the idea of an IOT also struck me.  I'm happy
to hear that it seems like I was on the right path anyway.  :)

Thanks much, Adam!  This should give me more than enough to "fix" this issue
in my head!



Other related posts: