Re: Function in WHERE performance issue
Date: Mon, 13 Jul 2009 09:28:47 -0500 (CDT)
> 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,
> 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 */
> from geo_ip_org_loc
> where ip_num_end >= :integer_ip)
> Using a completely random IP address (google - 18.104.22.168)
> 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
> (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!
RichReceived on Mon Jul 13 2009 - 09:28:47 CDT