Indexes - Expert Oracle Database Architecture - page 442

For comparison reasons, we run this script not only with one column, but also two and three compressed
columns, to see what happens. At the end, we query IDX_STATS and should observe this:

EODA@ORA12CR1> select what, height, lf_blks, br_blks,
2 btree_space, opt_cmpr_count, opt_cmpr_pctsave
3 from idx_stats
4 /
WHAT HEIGHT LF_BLKS BR_BLKS BTREE_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
------------- ---------- ---------- ---------- ----------- -------------- ----------------
noncompressed 2 227 1 1823120 2 28
compress 1 2 206 1 1654380 2 21
compress 2 2 162 1 1302732 2 0
compress 3 2 268 1 2149884 2 39

We see that the COMPRESS 1 index is about 90 percent the size of the noncompressed index (comparing
BTREE_SPACE ). The number of leaf blocks has decreased measurably. Further, when we use COMPRESS 2 , the savings
are even more impressive. The resulting index is about 71 percent the size of the original. In fact, using the column
OPT_CMPR_PCTSAVE , which stands for optimum compression percent saved or the expected savings from compression,
we could have guessed the size of the COMPRESS 2 index:

EODA@ORA12CR1> select 1823120*(1-0.28) from dual;

1823120*(1-0.28)
----------------
1312646.4

■ the ANALYZE command against the noncompressed index populated the OPT_CMPR_PCTSAVE/OPT_CMPR_COUNT
columns and estimated a 28 percent savings with COMPRESS 2 , and we achieved just about exactly that.

Note

But notice what happens with COMPRESS 3 . The resulting index is actually larger: 117 percent the size of the original
index. This is due to the fact that each repeated prefix we remove saves the space of N copies, but adds 4 bytes of
overhead on the leaf block as part of the compression scheme. By adding in the OBJECT_NAME column to the compressed
key, we made that key almost unique—in this case meaning there were really no duplicate copies to factor out.
Therefore, we ended up adding 4 bytes to almost every single index key entry and factoring out no repeating data.
The OPT_CMPR_COUNT column in IDX_STATS is dead accurate at providing the best compression count to be used, and
OPT_CMPR_PCTSAVE will tell you exactly how much savings to expect.
Now, you do not get this compression for free. The compressed index structure is now more complex than
it used to be. Oracle will spend more time processing the data in this structure, both while maintaining the index
during modifications and when you search the index during a query. What we are doing here is trading off increased
CPU time for reduced I/O time. With compression, our block buffer cache will be able to hold more index entries
than before, our cache-hit ratio might go up, and our physical I/Os should go down, but it will take a little more CPU
horsepower to process the index, and it will also increase the chance of block contention. Just as in our discussion of
the hash cluster, where it might take more CPU to retrieve a million random rows but half the I/O, we must be aware of
the tradeoff. If you are currently CPU bound, adding compressed key indexes may slow down your processing. On the
other hand, if you are I/O bound, using them may speed up things.