I was also trying to figure out if this result can be 'generalized' for more than the specific example given. IE, is there another case where this solution would work. Unless I am missing something, which is entirely possible, this solution seems to be pretty specific to the case where you have no overlapping endpoints. On Mon, Jul 27, 2009 at 3:49 PM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx > wrote: > > I think the problem with the code is that it could return the > wrong result as it depends on Oracle using a specific index > in a specific way. > > The code towards the end of the response addresses > that issue, though, by including the extra subquery: > "where ip = (select min(ip) > :bind_ip)" > > Even if something goes wrong and Oracle uses an > undesirable access path it will still find the correct > value. > > 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: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx> > To: <andrew.kerber@xxxxxxxxx>; "ORACLE-L" <oracle-l@xxxxxxxxxxxxx> > Sent: Monday, July 27, 2009 9:42 PM > Subject: RE: range-bound queries > > > > Hi Andrew, >> >> The article is all Dan Tow. I can’t take credit for any authorship. It >> all started w/ a question I asked here, on Oracle-L, which Dan answered. He >> got the idea for the article, and asked if he could use my name and SQL as >> is, or if I wanted him to obfuscate. I told him to go ahead. But, the >> authorship is all him. >> >> As to the rownum=1, the whole idea there is to short circuit the range >> scan, as soon as you have a single match. It’s a way of implementing the >> idea behind the “least-greater-than-or-equal-to” and >> “greatest-less-than-or-equal-to” operators which he discusses in the >> article. >> >> To answer your questions, the code that Dan provided went into production >> shortly after he provided me that solution, and I believe it’s still going >> strong, 4 years later. Can you expand on your concerns about it being >> “safe”? To my knowledge, there’s nothing inherently dangerous about it. >> >> -Mark >> >> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] >> On Behalf Of Andrew Kerber >> Sent: Monday, July 27, 2009 4:30 PM >> To: ORACLE-L >> Subject: range-bound queries >> >> I saw an article recently (the article is actually dated 2005), by Dan Tow >> and Mark Bobak showing a method of speeding up range bound queries by >> qualifying on rownum=1 as part of the query. >> >> http://linuxdevcenter.com/pub/a/linux/2004/01/06/rangekeyed_1.html >> >> Has anyone been able to successfully do that with production SQL? >> >> If so, are there any special conditions that make this technique safe? >> >> Has anyone found another way to reproduce this effect? >> >> >> >> -- >> Andrew W. Kerber >> >> > > > -- > //www.freelists.org/webpage/oracle-l > > > -- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.'