Re: General question on index compression and performance

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:<b3ht1n$1m3glk$1_at_ID-152732.news.dfncis.de>...
> Hello,
>
> I would like to know if index compression can have *negative* performance
> impacts
> to applications.
>
> The background is this:
> On thursday 13th I reorganized some indexes which the COMPRESS clause
> of a certain user schema; I was hoping that besides of space savings on the
> disks, queries in the schema
> may become a little faster by doing this; of course, I did analyze the whole
> schema right afterwards
> (to be clear: it was not SYSTEM).
>
> The COMPRESS clause was used without any following number, and only if the
> index
> had a cardinality/rows ratio that afaik made index compression an
> appropriate action.
> My main reference was Guy Harrisons tuning book.
>
> Today (26th) I am hearing for the first time
> that users in that schema are recognizing performance loss
> "since about one week"
> with *any* app they are using in that schema. And the apps have not been
> changed
> .
> On the other hand, most of the users do not *dare*
> to call the admins about what is happening ... so it is not very reliable
> what they say ...
>
> Because I am not too experienced as many gurus here, I would like to ask for
> an eventual confirmation
> about index compression risks,
> or if still some more informations are needed to judge the situation. May I
> have to rebuild the indexes
> with NOCOMPRESS again ? It is yet not a "hot issue", I just want to be a
> little more sure ...
>
> My system:
> Oracle EE 8.1.7.3 on AIX 4.3.3 on a Bull server which is now 3 years old ...
> Oracle Forms & Reports inhouse app on Windows clients
>
> TIA
>
> Jan

Jan, yes I believe index compression sometimes hurts performance. Sometimes it helps. It depends on the data and how it is accessed.

But first, did you remember to re-analyze the table and its indexes after conversion of the one index to being compressed? Failure to do so rather than the compression itself could be the problem.

HTH -- Mark D Powell -- Received on Wed Feb 26 2003 - 08:02:52 CST