RE: Function in WHERE performance issue

Good point, Adam.  In my case, the not-found condition is sufficiently rare 
that it's not a concern.  But I do see your point.

-Mark

-----Original Message-----
From: Adam Musch [mailto:ahmusch@xxxxxxxxx] 
Sent: Monday, July 13, 2009 11:26 AM
To: Bobak, Mark
Cc: rjoralist@xxxxxxxxxxxxxxxxxxxxx; Oracle L
Subject: Re: Function in WHERE performance issue

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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>



-- 
Adam Musch
ahmusch@xxxxxxxxx


--
http://www.freelists.org/webpage/oracle-l


Other related posts: