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 - 74.125.45.100)
>
> 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!
Rich
--
//www.freelists.org/webpage/oracle-l
Other related posts:
