Re: Function in WHERE performance issue
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: "Oracle L" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 13 Jul 2009 14:18:05 -0500 (CDT)
Hey Adam,
> Select columns from geo_ip_org_loc
> where :integer_ip between ip_num_eng and ip_num_start
> and ip_num_end = (select /*+ no_unnest */
> min(ip_num_end)
> from geo_ip_org_loc
> where ip_num_end >= :integer_ip)
This works beautifully!
Select gl.*
from geo_blocks gb, geo_location gl
where gb.locid = gl.locid
and :p_ipnum between gb.startipnum and gb.endipnum
and endipnum =
(
select /*+ no_unnest */ min(endipnum)
from geo_blocks
where endipnum >= :p_ipnum)
The conversion to IOT was a big help to pin this query at 10 consistent gets
for me.
I'm still not sure why using the function in place of the converted IP
address causes the gets to jump to ~4500 though. Run alone against DUAL,
the function does 0 consistent gets, so 0 gets + 10 gets somehow = ~4500.
I'm not terribly concerned about this part, as I'll be confirming shortly
that when done in PL the LIOs will remain at ~10, but I'm durned curious at
this point. Too bad this thing called "work" keeps getting in the way.
Maybe that's a *good* thing...
Thanks again!
Rich
--
//www.freelists.org/webpage/oracle-l
Other related posts:
