RE: range-bound queries

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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

'If at first you dont succeed, dont take up skydiving.'
--

http://www.freelists.org/webpage/oracle-l Received on Mon Jul 27 2009 - 15:42:31 CDT