Re: Index question
Date: Wed, 26 Aug 2009 07:17:08 +0100
Message-ID: <roydnWW3d4L8TgnXnZ2dnUVZ8vSdnZ2d_at_bt.com>
<stevedhoward_at_gmail.com> wrote in message news:c0d7d88b-2269-490c-98e9-550c01b82a39_at_v36g2000yqv.googlegroups.com... On Aug 25, 5:32 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
>Thanks for your thoughts, Jonathan.
>
>This is interesting on a couple of fronts:
>
>1) I would define anything that returns incorrect data as
>"corruption" (or at least the people would that pay Oracle, and more
>importantly me ;)). I guess if the data itself is structurally
>correct, you're saying that if a different plan were selected, the
>correct data *would* be returned? That sounds like a nasty bug.
>
"Wrong results" - is not corruption and is always a P1 bug. You can get wrong results without physically perfect data. Try taking your "between" query putting it into an inline view and wrapping it with a "rownum = 1" predicate. You'll probably get a plan that gets you the min() with a different plan.
>
>2) I never knew an index rebuild could just scan the existing index
>and rebuild from that. That sounds like the definition of "garbage in
>garbage out". Why would Oracle do it this way?
>
Historically I believe people used to rebuild indexes because they thought
they have become inefficient - not because they thought they were corrupt.
If the data in the old index is correct it can make sense to read it in
order
rather than scanning the (probably larger) table extracting data and
sorting
it. The optimizer chooses - and there are three possible paths in total -
but "explain plan" seems to lie about which one will be used.
-- 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.htmlReceived on Wed Aug 26 2009 - 01:17:08 CDT