The only concern I'd have with Dan Tow's solution for Oracle -- adding AND ROWNUM=1 -- is that it doesn't shortstop the range scan in a not-found condition, which can definitely happen in the original data set. The nested subquery solution I posted before does. Rerunning the earlier query I had for 198.168.0.1 (which isn't in the data set): with rownum = 1, no subquery - 28109 CG's with rownum = 1, with subquery - 6 CGs without rownum = 1, with subquery - 6 CGs It's an 8.8 million row IOT I'm searching, so telling it to stop when it can't find a match anymore is an excellent idea. I think it should be able to stop once the search_ip > end_ip, but it doesn't seem to on its own. On Mon, Jul 13, 2009 at 9:30 AM, Bobak, Mark<Mark.Bobak@xxxxxxxxxxxx> wrote: > Rich, > > I had a similar problem a while back. Dan Tow provided me an excellent > solution, and also wrote it up as an articel, here: > http://linuxdevcenter.com/pub/a/linux/2004/01/06/rangekeyed_1.html > > I think it's worth your time to read. > > -Mark > ________________________________________ > From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf > Of Rich Jesse [rjoralist@xxxxxxxxxxxxxxxxxxxxx] > Sent: Monday, July 13, 2009 10:25 > To: Oracle L > Subject: Re: Function in WHERE performance issue > > Hey Stephane, > >>> 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(). > > But I haven't created an FBI. And while the addition of the above to the > WHERE clause does cause a sub-second return, reworking the statement to: > > select /* first_rows(1) */ aa.* > from geo_location aa, > geo_blocks b > where aa.locid = b.locid > -- and ip2number(:p_ipaddr) between b.startipnum and b.endipnum > and to_number(substr(:p_ipaddr, 1,instr(:p_ipaddr, '.') - 1)) * 16777216 > between b.startipnum and b.endipnum > > ...does not. While somewhat faster at ~5s, it's not sub-second. > >> And, please, remove the hint, it's ugly. > > I'd love to. I only have one hint on one query on this system and it's for > an outline. However, without the hint I get an FTS, which I know isn't > necessary, based on the sub-second response times I can get by changing the > predicate. Removing it would certainly be ideal. > > I'll probably be running this statement in PL, where I can parse the IP via > the function and then pass the result to the SQL via bind, so my question is > more academic. But I'm having a mental issue of generalizing the situation > so that I can apply it to other future issues like this. > > Thanks for the ideas! > > Rich > > -- > //www.freelists.org/webpage/oracle-l > > > > > -- > //www.freelists.org/webpage/oracle-l > > > -- Adam Musch ahmusch@xxxxxxxxx -- //www.freelists.org/webpage/oracle-l