ALTER INDEX REBUILD PARTITION ONLINE COMPRESS PARALLEL

After a frustrating period of quite a few minutes but less than an hour of trying various combinations, it seems to be that rebuilding an index partition online with compression is actually a two stage affair although if it explicitly states this in the documentation then I missed it. And I did an online search for similar and neither was the answer stated obviously in the first couple of pages of results or in the documents I was directed to.

I am doing some tests with enabling index key compression – a great feature by the way if it somehow passed you by over the many years it’s been around – and am expecting significant space savings.

In my particular case, it’s about time I played with some “newer” features and whilst the ADVANCED COMPRESSION HIGH scares me off a bit – not least because of some of the associated bug notes on Oracle Support for versions < 23cai – what I’ve read about ADVANCED COMPRESSION LOW holds for me fewer reservations as it seems pretty much like old style key compression but with some intelligence (heuristic not artificial) around optimal # of keys and whether that is suitable for every block.

So the two stage syntax would appear to be:

alter index <index_name> modify partition <partition_name> compress advanced low;
alter index <index_name> rebuild partition <partition_name> online parallel n;

Now as I write this, I have a horrible sense of deja vu that I’ve already written about this in the past but I had a look and I can’t find that either.