0

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?

2
  • There is no such statistic. You can find the full list in v$statname. Index entries are never updated. An update on an entry is actually an insert + delete (mark deleted). Commented Jan 29, 2016 at 10:16
  • What is it you are trying to achieve here? What's the end result you want? Commented Jan 29, 2016 at 10:42

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.