Re: Index Compression question - afterquestion

"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.

I though that you were correct but

SQL> create table tab1 as select * from dba_objects;

Table created.

SQL> create index idx1 on tab1(object_id,object_name);

Index created.

SQL> analyze table tab1 compute statistics;

Table analyzed.

<edit embarrasing typo>

SQL> ED
Wrote file afiedt.buf

  1 select blevel,leaf_blocks,distinct_keys   2 from dba_indexes
  3* where index_name = 'IDX1'
SQL> /     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ----------- -------------

         1 163 29544

SQL> alter index idx1 rebuild compress 1;

Index altered.

SQL> select blevel,leaf_blocks,distinct_keys   2 from dba_indexes
  3 where index_name = 'IDX1'
  4 ;

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ----------- -------------

         1 163 29544

So in other words the stats are left in place

SQL> analyze table tab1 compute statistics;

Table analyzed.

SQL> select blevel,leaf_blocks,distinct_keys   2 from dba_indexes
  3 where index_name = 'IDX1'
  4 ;

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ----------- -------------

         1 188 29544

NB it doesn't suprise me that leaf_blocks has gone up in this instance as object_id will be unique so is a daft candidate from compression.

9.2 on win2k.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************