oracle update indexs,update indexes vs rebuild index for table move

rp0428 wrote:Yes it can cause indexes to grow, updates on indexes always does DELETE old key INSERT new key, the DELETE action doesn't release space to the tablespace, only the leaf block so you would have to insert / update a row to that value in order to reuse the space.Again, it's probably not a problem. You can always do an online rebuild of the index afterwards if you're really concerned by space but IMO it is almost always a waste of effort.Never tried this (and not now in a position to) but thought you may have.1. exchange partition to work table2. compress the data to a new work table and build new indexes (assumes OP is using local partitioned indexes3. exchange the compressed table partition back to the real tableHave you ever done an exchange on a compressed table/indexes? Any reason why that won't work?

Testing on 11.2.0.3

Exchanging a basic compressed table segment with a non-compressed table partition segment works fine - data remains compressed.

If indexes on the table have differences in compression with the local indexes on the partitioned table then the exchange operation is still valid and indexes are still usable after.

Because you have to do the exchange twice, the updates to global indexes (if there are any) will take twice as long as they would have for the one move operation. The local indexes need to be rebuilt on the work table after the alter table move compress operation. I can't see an obvious benefit to this method as opposed to the move then rebuild, if there are any global indexes then you'll be doing more work. The table partition is empty for a period of time, which could cause issues. Obviously, the downsides depend a lot on the application and the business requirements but I think move - rebuild indexes will probably be more appropriate in most cases.

As an aside, I just had a read through the 12.2 new features as I remember there being some cool partitioning changes and I know 12.1 introduced asynchronous global index maintenance for drop and truncate partitions. But no, it's not been extended to support exchange partitions just yet (I'm aware that drop and truncate work nicely due to the index keys no longer pointing to table block and an asynchronous exchange would need to be much more complex but I'm always hopeful). There will be new DDL commands to make the work table easier to create for exchange though, so that's something.