RE: Index key compression - performance benefits?
- From: "Keith Moore" <kmoore@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 31 Aug 2006 11:55:30 -0500 (CDT)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
