Re: Function in WHERE performance issue
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: rjoralist@xxxxxxxxxxxxxxxxxxxxx
- Date: Mon, 13 Jul 2009 16:36:28 +0200
Rich,
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
select locid
from geo_blocks
where ip2number('192.168.1.1') between b.startipnum and b.endipnum;
Oracle reads
select something
from a_table
where <constant> between C1 and C2
in other words
select something
from a_table
where C1 <= <constant>
and C2 >= <constant>
The index on (C1, C2) doesn't look very tempting ... Two unbounded
conditions.
Now add
and C1 >= <other constant>
suddenly, a range scan on the index looks like a much better option ...
HTH
SF
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;
>>>
>>>
>> 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:
