RE: Index key compression - performance benefits?

Thanks. I either forgot or never knew about this compression calculation.

There is a problem with running it on production as it locks the table unless done online and if 'online' is specified, the stats are not generated.

I ran the command in a test system and all the stats were generated except compression. Any idea why? It is Oracle 9.2.0.4, 64 bit on Solaris.

analyze index xxx validate structure;

select name, opt_cmpr_count, opt_cmpr_pctsave, height, blocks, lf_rows from index_stats;

NAME OPT_CMPR_COUNT OPT_CMPR_PCTSAVE HEIGHT BLOCKS LF_ROWS

-------- -------------- ---------------- ---------- ---------- ----------
XXX                   0                0          4     573952  207319622

I then dropped and recreated the index with 'compress 1' and got about 50% compression.

Keith

>
> AFAIK Steve wrote the script for 8i only because as of 9i Oracle does it
> with the ANALYZE statement... i.e. no need to have a script. Here an
> example:
>
> SQL> create table t as select * from all_objects;
>
> Table created.
>
> SQL> create index i on t(owner, object_type, object_name) ;
>
> Index created.
>
> SQL> analyze index i validate structure;
>
> Index analyzed.
>
> SQL> select opt_cmpr_count, opt_cmpr_pctsave from index_stats;
>
> OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
> -------------- ----------------
> 2 28

--
http://www.freelists.org/webpage/oracle-l