Re: Index Compression question - afterquestion

"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 Tue Jan 21 2003 - 08:07:51 CST