Is there a way to make extent sizes smaller (64 K) while compressing sub-partitions of a table that
In my 11g R2 database, I have a large table that is interval-partitioned and hash-subpartitioned:
CREATE TABLE pathsens (...)
PARTITIONED BY RANGE (valuationuid) INTERVAL (1)
SUBPARTITION BY HASH (pathuid) SUBPARTITIONES 16
(
PARTITION dummy VALUES LESS THAN (1)
)
STORAGE(INITIAL 8M next 8m);
The initial size 8M is too big. It takes ~4 M rows to fill the 1st extent of all 16 sub-partitions. In many cases, partitions have less than 4 M rows. Therefore, when I try to compress the table using ...
ALTER TABLE pathsens MOVE SUBPARTITION SYS_SUBP119285 PARALLEL 16 COMPRESS FOR QUERY HIGH;
... I do not get any disk space back because the new extents are still 8M in size and there are still 16 of them.