FAQ
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

Search Discussions

  • Luis at Dec 20, 2012 at 9:21 am
    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 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
  • Steve Wales at Dec 20, 2012 at 4:05 pm
    Thanks for the responses.

    I got my issue resolved, I was more asking just from a learning experience
    more than anything to get a better understanding of how the optimizer works.

    A better understanding of what the optimizer does is on my "to do" list for
    the coming year.

    Cheers,
    Steve
    _____

    From: Luis
    Sent: Thursday, December 20, 2012 2:21 AM
    To: sjwales@comcast.net
    Cc: list
    Subject: Re: How the optimizer handles a range of values


    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?
  • Niall Litchfield at Dec 20, 2012 at 7:17 pm

    On Dec 20, 2012 4:06 PM, "Steve Wales" wrote:
    A better understanding of what the optimizer does is on my "to do" list for
    the coming year.
    You wont be alone. Partly because there's always more oddities (I know
    Jonathan Lewis tends to refer to edge cases but I'm not entirely happy with
    that term, at least not without an explanation of what the edge is). Partly
    because almost certainly 12c and the next 11.2 patch set will make
    interesting changes.

    I'd recommend starting with Wolfgang Breitlings paper and then getting a
    copy of Jonathan's optimizer book. *and running the examples*. Good luck.
  • Mohamed houri at Dec 20, 2012 at 9:34 am
    Hello,
    And what is the definition of the other index your query is
    desiring(prefering)?

    The clustering factor of the unpicked index and the preferred one might
    play a role here in your case.


    Best regards
    Mohamed Houri
    www.hourim.wordpress.com

    2012/12/20 Steve Wales <sjwales@comcast.net>
    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


    --
    Bien Respectueusement
    Mohamed Houri


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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 19, '12 at 11:50p
activeDec 20, '12 at 7:17p
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase