Re: ANALYZE INDEX ...

On Mon, 22 Nov 1999 13:02:11 +0100, "Sybrand Bakker" <postmaster_at_sybrandb.demon.nl> wrote:
> 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.

I see. I think this is it. Thanks.

On one of the fields I would get about 3/5 of table, on the second about 1/6 and the third anout 1/4. Since those are orthogonal I should get about 1/40 of the table with all at once.

> 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.

I wasn't sure analyzing table analyzes all indexes. Anyway in this case I created the index AFTER I analyzed the table, so it made a difference.

> From here I can't decide which remarks apply.
> Hth,
>
> --
> Sybrand Bakker, Oracle DBA

Thanks a lot, Jenda Received on Mon Nov 22 1999 - 09:25:55 CST