Re: Function in WHERE performance issue
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:
http://jonathanlewis.wordpress.com/2006/11/06/filter-subqueries/
Deterministic Functions: http://www.jlcomp.demon.co.uk/deterministic.html
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
- Original Message ----- From: "Rich Jesse" <rjoralist_at_society.servebeer.com> To: "Oracle L" <oracle-l_at_freelists.org> Sent: Friday, July 10, 2009 10:45 PM Subject: Function in WHERE performance issue
Hey all,
In 10.1.0.5.0, I have two tables of IP and lat/long info of the CSV data from http://www.maxmind.com/app/geolitecity. 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:
http://cbmc64.blogspot.com/2005/05/oracle-plsql-function-to-convert_30.html
...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('192.168.1.1') between b.startipnum and b.endipnum;
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jul 10 2009 - 17:00:56 CDT