Oracle actually drops the Index segment (so you don't see it in USER_SEGMENTS) when it is set to UNUSABLE alhough the Index definition is still present. The Index doesn't "grow" as the Segment doesn't exist.

Immediately after the TRUNCATE TABLE, the Index Segment is instantiated and the Index becomes VALID again. So inserting rows will update the Index. My last explicit command against the Index was ALTER INDEX ... UNUSABLE but that seems to be not the current state now !

18 May, 2016

As demonstrated in the first post in this series, the default size for Table Partitions is to start with an I nitial8MB Extent. The fourth post demonstrated how to resize a Partition that has already been created and populated.

Here is a method to pre-size the Initial Extent. I start with 3 partitions of different sizes.

I have updated 220,000 rows without actually increasing the notional length of each row (I set OWNER=OWNER). Yet, The CHAIN_CNT is now 202K and the table's HighWaterMark has expanded from 4,452 blocks to 7,882 blocks. A significant increase !
(YMMV may vary in your tests !)

It was Jonathan Lewis who suggested getting the Chain Count (or LIST CHAINED ROWS) to understand the impact of UPDATEs on a table with BASIC compression.
.
.
.

05 May, 2016

Following up on my earlier post on 12.1.0.2 Advanced Index Compression, one of my readers asked what would be the difference if I reversed the order of columns in the chosen index.

My defined index was on (OWNER, OBJECT_TYPE, OBJECT_NAME) --- defined as being from the column with the fewest distinct values to the most. This ordering is best compressible with Index Key Compression (also known as Prefix Compression). If I reverse the order, Index Key Compression for the two leading columns wouldn't deliver the same level of compression. The question is whether Advanced Index Compression can intelligently handle the reversal.

This is, again, as expected. Advanced Index Compression results in the same size irrespective of the ordering of the columns.

The advantage of Advanced Index Compression over Key or Prefix Compression is that the DBA does not need to determine the Prefix for compression. He does not have to spend time to analyze the data and compare the number of distinct values for each of the columns in the composite index.
.
.
.