Re: range-bound queries
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 27 Jul 2009 21:49:02 +0100
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
Other related posts:
