The problem only exists because indexed information is not HCC compressed in Exadata, so the database ends up having tables 10 times smaller, but indexes stay the same.
Well the idea came to me through Ghassan Salem from RWP and hence the credits belong to him. If the table is partitioned, you can save space by marking unusable the old index partitions (so they will be wiped out saving space). According to Ghassan even if those old partition’s are accessed, the absence of an indexed can be compensated by having those partitions with a higher compression level, and so the Full Partition Scan will not be noticed. Great idea? But how can we do this?
After googling a bit and browsing the Oracle docs, it turned out that a blog post explaining how to implement Ghassan’s idea already existed. It’s from Christian Antognini the author from the book: “Troubleshooting Oracle Performance”.
This is truly a great idea and instead of loosing my time and energy trying to make it my own, I’d rather share it and acknowledge it through this blog post.
LMC
