RE: Function in WHERE performance issue
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "rjoralist@xxxxxxxxxxxxxxxxxxxxx" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>, Oracle L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 13 Jul 2009 10:30:18 -0400
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
Other related posts:
