Re: range-bound queries

From: Jonathan Lewis <>
Date: Mon, 27 Jul 2009 21:49:02 +0100
Message-ID: <96CBA07A85D7431FBD4F9AA5CFC7C1FA_at_Primary>

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.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

  • Original Message ----- From: "Bobak, Mark" <> To: <>; "ORACLE-L" <> 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: [] On
> Behalf Of Andrew Kerber
> Sent: Monday, July 27, 2009 4:30 PM
> 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.
> 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

Received on Mon Jul 27 2009 - 15:49:02 CDT

Original text of this message