Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: ANALYZE INDEX ...

Re: ANALYZE INDEX ...

From: Sybrand Bakker <postmaster_at_sybrandb.demon.nl>
Date: Mon, 22 Nov 1999 13:02:11 +0100
Message-ID: <943272203.22701.0.pluto.d4ee154e@news.demon.nl>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US