Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: ANALYZE INDEX ...
In theory Oracle should be able to merge non-concatenated indexes, but this
only seldom happens automatically.
Creating a concatenated index is the correct approach.
After your ANALYZE INDEX (which computes statistics) several remarks may
apply:
1 You now are using the Cost Based Optimizer, which tries to minimize IO,
and doesn't use heuristics.
2 You may have an index that's unselective. The guideline is, whenever an
index retrieves more than 10 percent of the table a full table scan will
provide better results.
3 You may not have ANALYZEd the TABLE (which will automatically analyze
indexes), so in this case you are fooling CBO in something it shouldn't do.
From here I can't decide which remarks apply.
Hth,
--
Sybrand Bakker, Oracle DBA
Jenda Krynicky <Jenda_at_Krynicky.cz> wrote in message
news:1103_943268763_at_prague_main...
> I think I should better RTFM but there's tooooo much of it so ...
>
> Thanks for any help.
>
> I've noticed something strange.
>
> 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).
>
> Jenda
> http://Jenda.Krynicky.cz
>
Received on Mon Nov 22 1999 - 06:02:11 CST