Re: Function in WHERE performance issue
Date: Mon, 13 Jul 2009 16:36:28 +0200
You misread me. I didn't tell you to REPLACE what you add with the condition I gave you, but simply to ADD the new condition.
OK, I'm going to try to explain what happens. You have a start range and an end range value, and a PK on (start, end). But there is a big semantic gap (I love big words) between what you know and what Oracle knows. For instance, you know that the end range value is higher than the start range. Oracle doesn't.
When you write
where ip2number('192.168.1.1') between b.startipnum and b.endipnum;
where <constant> between C1 and C2
in other words
where C1 <= <constant>
and C2 >= <constant>
The index on (C1, C2) doesn't look very tempting ... Two unbounded conditions.
and C1 >= <other constant>
suddenly, a range scan on the index looks like a much better option ...
Rich Jesse wrote:
> 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;
>> 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
> 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!