Oracle index usage

Question

I came across V$SEGMENT_STATISTICS dynamic view.

What statistic should I consider to tell if index block is being updated from time to time (on the contrary to scenario where index block is created once and it's only read since then.

I want to find out only the indexes that are updated. db block changes as I understand it, does not distinguish insert/update/delete operations. Change is change and not update solely.

I just want to prepare list of indexes that can be compressed. As I understand if index is frequently modified over the time, maybe it's better not to put additional overhead as index compression. Does this make sense?

Answer 1

We can compress B*Tree indexes. This removes redundancies from concatenated indexes. We dont get compression for free. Oracle spends more time processing the compressed data while maintaining and searching the index during query execution. Compression may increase CPU time(Extra processing overhead) while reducing I/O time. so be aware of tradeoff.
Normally if you are CPU bound, compressing the key index may slow down your processing, if you are I/O bound then using compressed key index may speed up the processing and reduces number of I/Os as well.

You can use ANALYZE INDEX index_name VALIDATE STRUCTURE to gather statistic. Then use INDEX_STATS to get the information(Height, BTree Space, Optimal Compress Percentage Save and more) about the index. Do some mathematics using these statistics then decide whether to compress your index or not.