Hello everyone,
I am in a doubt about moving a old table partition, whether I should use update indexes or not and rebuild indexes later.
My tables partitioned by ranged, every month is a partition on my table. every month, I will compress previous month partition (in our system last month data is not used, so I can compress it safely) but while doing that I can use "update indexes" clause or I can rebuild indexes after compress operation (or use update global indexes and rebuild locally partitioned indexes relevant partition).
Here is my problem, using update indexes takes less time than rebuilding all indexes (I have too much indexes on the tables about 10-20). Also update indexes generates less redo log, if I rebuild all indexes, data guard archive count go crazy. until now using update indexes is look like better to me but if I use update indexes, index's size is getting bigger than it should be. as if it creates too many empty leafs on indexes, so while I try to gain some space by compressing partition I losing really too much space from indexes.
for ex:
partition size 340MB
an index size (global index) 10.000MB
after compression with update indexes
partition size 100MB (approximately)
index size 10.300MB
it seems to me update indexes clause cause to index getting bigger. I gain 240 MB from partition but lost 300MB * index count.
so what do you think, should I use update indexes or rebuild indexes. what kind of method do you use on your own databases to compress old partitions?
thanks.