This discussion is archived
4 Replies Latest reply: Jan 10, 2012 8:51 AM by Guyver I RSS

An alternative to ANALYZE INDEX to find OPT_CMPR_COUNT?

Guyver I Newbie
Currently Being Moderated
Hello,
The database I'm using is Oracle 10g R2 Standard Edition, and I don't have additional licenses, such as Advanced Compression, nor can I use table compression. I've traditionally use ANALYZE INDEX to help me find the best compression ratio to rebuild an index using compression to save disk space on archived data. After analyzing an index, I use the resulting OPT_CMPR_COUNT value as the COMPRESS clause when I rebuild an index:

ANALYZE INDEX owner.index_name VALIDATE STRUCTURE OFFLINE;

SELECT OPT_CMPR_COUNT
FROM index_stats
WHERE name = 'owner.index_name';

+ALTER INDEX owner.index_name REBUILD COMPRESS {value from OPT_CMPR_COUNT};+

I'm aware that the ANALYZE statement is now deprecated. Is there an alternative to get the OPT_CMPR_COUNT value of an index, or is there different method to find the recommended compression ratio in Oracle 10g? Thank you.

-Sho Fukamachi

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points