Re: Index question

From: Jonathan Lewis <>
Date: Wed, 26 Aug 2009 07:17:08 +0100
Message-ID: <>

<> wrote in message On Aug 25, 5:32 pm, "Jonathan Lewis" <> 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.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Wed Aug 26 2009 - 01:17:08 CDT

Original text of this message