Re: General question on index compression and performance

Hi Mark,

yes I did analyze the table (it was only one, but a big one) and its indexes right after
rebuilding, with compute statistics as always. If You would know something more
in this case, please let me know.

Some people answered directly to me, thank You very much.

Jan

"Mark D Powell" <Mark.Powell_at_eds.com> schrieb im Newsbeitrag news:2687bb95.0302260602.12982332_at_posting.google.com... > "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 --