Re: range-bound queries
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Mon, 27 Jul 2009 16:06:08 -0500
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.'
Other related posts:
