Re: Index Compression question - afterquestion - Thanks!

Thank You, Richard, Andy, Niall and Howard, for your responses.

Before doing compression of about 80 indexes, I ran a skript checking the cardinalities of all indexes. And only if I found a cardinality lower than eg. 60 among 100 rows, I compressed the index.

I realized that compressing indexes that have same row number and cardinality number, they become *bigger* as before - and Richard gave the explanation why.

Now the entire schema is re-analyzed, and things here are running normal again.

It was jut the first time for me doing index compression, and so I wanted to make sure that if there are drawbacks, they would not harm performance.

And I see that things are ok now, index compression *can* be recommended, provided that
one keeps an eye on sufficient cardinalities and keeps statistics in shape.

BTW, I spared 1.5 GB space, after all ...

I have learned a lot from You all,

so thank You very much.

Jan=)

"Richard Foote" <richard.foote_at_bigpond.com> schrieb im Newsbeitrag news:clbX9.29569$jM5.76686_at_newsfeeds.bigpond.com...
> "Andy" <andy.spaven_at_eps-hq.co.uk> wrote in message
> news:GJ9X9.3579$9R.12250166_at_newsr2.u-net.net...
> > Howard / Niall / Jan
> >
> > Yep - just checked 8.1.7 as well - it doesn't appear to wipe stats on a
> > rebuild. There goes another assumption lurking as a false training
memory
> > :-). Just goes to show. That now leaves Jan's question about poor
> > performance after the rebuild unanswered. If it was okay before and we
> know
> > the stats haven't changed what caused a slow down ?
> >
>
> Going back to the question, does index compression have any disadvantages,
> the answer is unfortunately my standard, boring "it depends".
>
> As the old say goes, "what gets compressed, needs to be uncompressed (is
> that a word ?)". And this requires extra work and extra resources in the
> form of CPU. Now the question that needs to be asked is "are the savings
we
> get from hopefully fewer leaf blocks greater than the additional costs of
> uncompressing the data ?". And this "depends" on how efficient we are at
> compressing the index which in turn depends on the cardinality of the
> column(s) of the index. Note that the "prefix" values used in compression
> takes additional storage and if the cardinality is high (meaning we have
too
> many distinct values for the compressed column(s) in each individual leaf
> block) we may even turn out and use more storage not less. So simply
> compressing indexes isn't sufficient, we need to make sure they're
> appropriate for compression as well. Oracle helps a touch by not allowing
> compression on single column unique indexes but everything else is pretty
> well fair game.
>
> Now I don't have enough information to suggest that this is the reason for
> the drop in performance but if poor index selections are made for
> compression, then some drop in performance and increase in CPU is to be
> expected.
>
> Make sense ?
>
> Richard
>
>
Received on Wed Jan 22 2003 - 03:26:23 CST