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