Re: Index Compression question - afterquestion

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Mon, 20 Jan 2003 20:40:22 +1100
Message-ID: <pkPW9.28659$jM5.73748@newsfeeds.bigpond.com>

A couple of tests, then:

SQL> create index compidx on emp(deptno, ename); Index created.

SQL> select avg_leaf_blocks_per_key
  2 from dba_indexes
  3 where index_name='COMPIDX';

AVG_LEAF_BLOCKS_PER_KEY

---

[notice the lack of statistics for a freshly-created index]

SQL> analyze index compidx compute statistics; Index analyzed.

SQL> select avg_leaf_blocks_per_key
  2 from dba_indexes
  3 where index_name='COMPIDX';

AVG_LEAF_BLOCKS_PER_KEY

---

                      1

[And after the computation of statistics, we have a result]

SQL> alter index compidx rebuild;
Index altered.

SQL> select avg_leaf_blocks_per_key
  2 from dba_indexes
  3 where index_name='COMPIDX';

AVG_LEAF_BLOCKS_PER_KEY

---

                      1

And notice that after a regular rebuild, we *still* have a result. So a regular rebuild does NOT destroy prior statistics.

Now, how about compression?

SQL> alter index compidx rebuild compress; Index altered.

SQL> select avg_leaf_blocks_per_key
  2 from dba_indexes
  3 where index_name='COMPIDX';

AVG_LEAF_BLOCKS_PER_KEY

---

                      1

Er, so rebuilding with compression doesn't destroy statistics, either.

Of course, the whole point of adding compression is that your index should be very much smaller afterwards than before, so if the old statistics are left there, you've only done half a job, and they'll be extremely misleading. But, regarding specifically whether rebuilding with compression wipes statistics, no it doesn't.

Though this is 9i Release 2, on Windows, so it could be different in 8i or on other O/Ses (but I doubt it).

Regards
HJR "Andy" <andy.spaven_at_eps-hq.co.uk> wrote in message news:4uOW9.3530$9R.12191378_at_newsr2.u-net.net...
> Jan
>
> Not OS swapping - the move from non compressed to compressed indexes - if
> you'd done ALTER INDEX ... REBUILD COMPRESS COMPUTE STATISTICS NOLOGGING
> things might have been okay. Without the COMPUTE STATISTICS keywords I
> think you loose the statistics already gathered (much like dropping and
> recreating an index). Someone correct me if I'm wrong on this point.
>
> Andy
>
> "Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message
> news:b0bkqt$o4r2s$1_at_ID-152732.news.dfncis.de...
> > Hi, Andy ...
> >
> > "Andy" <andy.spaven_at_eps-hq.co.uk> schrieb im Newsbeitrag
> > news:mlVV9.3528$9R.12113872_at_newsr2.u-net.net...
> > > Jan
> > >
> > > I suspect that your stats were up to date but that the process of
> swapping
> > > to compressed destroyed them.
> >
> > please excuse me, I am not sysad,
> > could You be so kind to explain
> > how OS swapping can destroy statistics in the data dactionary ?
> >
> > > Did you drop and recreate the indexes or
> > > rebuild them ? If you rebuilt them did you specify compute statistics
> > > clause otherwise you loose stats (I believe) ?
> > >
> > > Andy
> > >
> >
> > This is what I did:
> >
> > ALTER INDEX ...
> > REBUILD
> > (no)compress --depends on the index
> > nologging
> > /
> >
> > And I always use COMPUTE to fresh up statistics
> > using ANALYZE or DBMS_UTILITY.
> >
> > P.S. As Daniel mentioned, I should use DBMS_STATS instead ...
> >
> > Jan
> >
> >
> >
>
>
Received on Mon Jan 20 2003 - 03:40:22 CST