RE: Index key compression - performance benefits?

Keith

> Personally, I am doubtful that the performance improvement will be
> very great. My understanding is that performance will only be
> improved in cases where you are doing a full index scan or a large
> index range scan.

As usually it depends. In some situation, typically when lots of accesses are performed, even saving few I/O (logical or physical it doesn't matter) per access could be very interesting. I.e. even range scans reading few blocks may be improved in this way.

> The query used to identify the indexes came from Steve Adam's web
> site. It is shown below. While I don't understand all the
> calculations involved, I can see that it orders the results by the
> amount of space saved (greatest of one_save and full_save) and does
> not relate directly to performance.
>
> The web site indicates the query works with 8i, but has not (yet)
> been tested with 9i. Does anyone know if it works with Oracle 9i?

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

HTH
Chris

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