Re: How the optimizer handles a range of values

From: Luis <lcarapinha_at_gmail.com>
Date: Thu, 20 Dec 2012 09:20:35 +0000
Message-ID: <CANx=q_Z-Ua3CwRG=27WyJLSAihiTE03R4gvtfVerLTSn9rKdbA_at_mail.gmail.com>

Hi Steve,
AFAIK there is no really difference between "between" and >=, <= since Oracle internally translate between clause into < / > clauses, but maybe someone knows better than me :-)

In the end of your explain plan you have Access and Filter predicates (BETWEEN query). They must be different since access predicate is for row find (lower bond) and filter for row filtering after find (higher bond).

I'd say that for both querys this access and filter predicates need to be equal.

You have also 10053 trace file to the rescue..

Can you show us the explain plan for both querys?

On Wed, Dec 19, 2012 at 11:49 PM, Steve Wales <sjwales_at_comcast.net> wrote:

> I'm doing some analysis of a query and looking at the indexes vs the query.
>
> The table has several indexes and one of them is on district+period+a few
> other columns.
>
> The query is doing this:
>
> where district = 'ABC'
> and period between '201210' and '201211'
>
> I'm wondering (and the execution plan's really not showing my much here, it
> still is picking up a completely different index anyway, which I can't
> explain yet) if there's any difference between this:
>
> where district = 'ABC'
> and period between '201210' and '201211'
>
> and this:
>
> where district = 'ABC'
> and period >= '201210'
> and period <= '201211'
>
> Been talking to Google, but haven't found anything yet.
>
> (Oh we're talking Oracle 9207 on HPUX 11.11)
>
> Thanks
> Steve
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Cumprimentos,
Luís Marques

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 20 2012 - 10:20:35 CET