RE: range-bound queries

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
  • To: "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 27 Jul 2009 17:17:36 -0400

Hi Jonathan,

Are wrong results possible?  I agree that it's possible that Oracle may not do 
what's intended, and that could cause a big performance problem, but I don't 
think it will ever return wrong results, will it?

I agree, the solution you point out in the comment is a good idea.  I'm just 
saying that the way Dan has it written, though poor performance could result, I 
don't think a wrong result is possible.

In my case, since the code has been in production for 4+ years, I'm probably 
not going to change it, unless we actually run into a problem.....


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Jonathan Lewis
Sent: Monday, July 27, 2009 4:49 PM
Subject: Re: range-bound queries

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


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

----- 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
> 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


Other related posts: