Re: Function in WHERE performance issue

From: Jonathan Lewis <>
Date: Fri, 10 Jul 2009 23:00:56 +0100
Message-ID: <679C7352EBB34EBE99F50C05DDE20AF5_at_Primary>

I think these two notes explain what's happening:

Scalar subquery caching:

Deterministic Functions:


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

  • Original Message ----- From: "Rich Jesse" <> To: "Oracle L" <> Sent: Friday, July 10, 2009 10:45 PM Subject: Function in WHERE performance issue

Hey all,

In, I have two tables of IP and lat/long info of the CSV data from PK on the location table is locid. PK on the block table is startipnum and endipnum, with a non-unique index on locid. There is an FK in the blocks table on location.locid.

I created the IP-to-number function from here:

...and called it "ip2number" and made it DETERMINISTIC. I then tried this simple statement:

select /*+ first_rows(1) */ aa.*
from geo_location aa,
geo_blocks b
where aa.locid = b.locid
and ip2number('') between b.startipnum and b.endipnum;

-- Received on Fri Jul 10 2009 - 17:00:56 CDT

Original text of this message