Re: ANALYZE INDEX ...

From: -=< a q u a b u b b l e >=- <aquabubble_at_Remove.No.Parma.Ham.Remove.geocities.com>
Date: Mon, 22 Nov 1999 12:01:45 -0000
Message-ID: <81c4ch$d4n$1@news4.svr.pol.co.uk>

Jenda Krynicky <Jenda_at_Krynicky.cz> wrote in message news:1103_943268763_at_prague_main...
<snip>
> I have run "EXPLAIN PLAN" on a SELECT STATEMENT like this :
>
> SELECT whatever, including(some,functions,like,SUBSTR) from Table
> WHERE Field1 = 'const1' AND Field2 = 'const2' AND Field3 = 'const3';
>
>
> I have created separate indexes for all these three fields, but the
EXPLAIN PLAN returned
> "TABLE ACCESS FULL ..."
>
> So I thought maybe Oracle is not able to use the indexes (I think it
should, but ...) so I created one index
> for all three fields.
>
> And EXPLAIN PLAN said the SELECT will use the index.
>
> Then I "ANALYZED" the index (whatever that means) and voila ... EXPLAIN
PLAN once again returns
> "TABLE ACCESS FULL ...".
>
> What's going on in here ? What am I doing wrong?
>
> FYI, the table has about one million rows, all the fields in question are
CHAR(something).

Are any of these char fields indexed? Which optimiser mode are you using? If you are in CBO, it would be that in Oracle's opinion it is more efficient to use the full table scan... it depends on how many rows you are expecting to return. You can force the use of indexes using optimiser hints if you really think that would be better.

HTH Received on Mon Nov 22 1999 - 06:01:45 CST