Partition Storage -- 4 : Resizing Partitions

Building on Posts 2 (Adding Rows) and 3 (Adding Partitions) where we saw Table Partitions using 8MB Extents ..... is there a way to "resize" Partitions to smaller Extents (and, maybe, lesser space consumed) without using Compression ?

Let's explore.

Beginning with Partitions P_100 and P_200 ....

SQL> select segment_name, partition_name, bytes/1024, extents
  2  from user_segments
  3  where segment_name like 'MY_PART_%'
  4  and partition_name in ('P_100','P_200')
  5  order by 1,2
  6  /

SEGMENT_NAME                   PARTITION_NA BYTES/1024    EXTENTS
------------------------------ ------------ ---------- ----------
MY_PART_TBL                    P_100             24576          3
MY_PART_TBL                    P_200             32768          4
MY_PART_TBL_NDX                P_100             28672         43
MY_PART_TBL_NDX                P_200             33792         48

SQL> 
SQL> alter table my_part_tbl move partition p_100 storage (initial 64K next 64K);

Table altered.

SQL> alter index my_part_tbl_ndx rebuild partition p_100 storage (initial 64K next 64K)
  2  /

Index altered.

SQL> alter table my_part_tbl move partition p_200 storage (initial 64K next 64K);

Table altered.

SQL> alter index my_part_tbl_ndx rebuild partition p_200 storage (initial 64K next 64K)
  2  /

Index altered.

SQL> 
SQL> select segment_name, partition_name, bytes/1024, extents
  2  from user_segments
  3  where segment_name like 'MY_PART_%'
  4  and partition_name in ('P_100','P_200')
  5  order by 1,2
  6  /

SEGMENT_NAME                   PARTITION_NA BYTES/1024    EXTENTS
------------------------------ ------------ ---------- ----------
MY_PART_TBL                    P_100             20480         35
MY_PART_TBL                    P_200             21504         36
MY_PART_TBL_NDX                P_100             18432         33
MY_PART_TBL_NDX                P_200             19456         34

SQL> 
SQL> select partition_name, blocks, num_rows
  2  from user_tab_partitions
  3  where table_name = 'MY_PART_TBL'
  4  and partition_name in ('P_100','P_200')
  5  order by 1
  6  /

PARTITION_NA     BLOCKS   NUM_ROWS
------------ ---------- ----------
P_100              3022    1100001
P_200              3668    1100001

SQL> exec dbms_stats.gather_table_stats('','MY_PART_TBL',granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select partition_name, blocks, num_rows
  2  from user_tab_partitions
  3  where table_name = 'MY_PART_TBL'
  4  and partition_name in ('P_100','P_200')
  5  order by 1
  6  /

PARTITION_NA      BLOCKS  NUM_ROWS
------------ ---------- ----------
P_100              2482    1100001
P_200              2639    1100001

SQL> 
SQL> 
SQL> l
  1  select partition_name, blocks, count(*)
  2  from dba_extents
  3  where owner = 'HEMANT'
  4  and segment_name = 'MY_PART_TBL'
  5  and segment_type = 'TABLE PARTITION'
  6  and partition_name in ('P_100','P_200')
  7  group by partition_name, blocks
  8* order by 1,2
SQL> /

PARTITION_NA     BLOCKS   COUNT(*)
------------ ---------- ----------
P_100                 8         16
P_100               128         19
P_200                 8         16
P_200               128         20

SQL> 

Partition P_100 has shrunk from 3 extents of 8MB adding up to 24,576KB to 35 extents adding up to 20,480KB. The High Water Mark has shrink from 3,022 blocks to 2,482 blocks (Remember : P_100 was populated with a Serial Insert.  Partition P_200 that had been populated with Parallel (DoP=4) insert has also shrunk from 32,768KB to 21,504KB and the High Water Mark from 3,668 blocks to 2,639 blocks.  The Extents are a combinaion of 64KB (the first 16, adding up to 1MB) and 1MB sizes.
Even the Index Partitions seem to have shrunk.

So, a MOVE/REBUILD (the REBUILD of the Index Partitons was required because I did a Partition MOVE without UPDATE INDEXES), could be used to shrink the Partitions with newer, smaller, Extents allocated.

But what about the case of SPLIT Partition, where Partitions SPLIT from an 8MB Partition resulted in 2 8MB Partitions, even for empty Partitions.

Here's a workaround.  Before SPLITting the P_MAX Partition, I resize it.

SQL> alter table my_part_tbl move partition p_max storage (initial 64K next 64K);

Table altered.

SQL> alter index my_part_tbl_ndx rebuild partition p_max storage (initial 64K next 64K);

Index altered.

SQL> alter table my_part_tbl
  2  split partition p_max
  3  at (1001)
  4  into (partition p_1000, partition p_max)
  5  /

Table altered.

SQL> alter table my_part_tbl
  2  split partition p_1000
  3  at (901)
  4  into (partition p_900, partition p_1000)
  5  /

Table altered.

SQL> alter table my_part_tbl
  2  split partition p_900
  3  at (801)
  4  into (partition p_800, partition p_900)
  5  /

Table altered.

SQL> 
SQL> l
  1  select segment_name, partition_name, bytes/1024, extents
  2  from user_segments
  3  where segment_name like 'MY_PART_%'
  4* order by 1,2
SQL> 
SQL> /

SEGMENT_NAME                   PARTITION_NA BYTES/1024    EXTENTS
------------------------------ ------------ ---------- ----------
MY_PART_TBL                    P_100             20480         35
MY_PART_TBL                    P_200             21504         36
MY_PART_TBL                    P_300              8192          1
MY_PART_TBL                    P_400              8192          1
MY_PART_TBL                    P_600              8192          1
MY_PART_TBL                    P_680              8192          1
MY_PART_TBL                    P_700              8192          1
MY_PART_TBL                    P_800                64          1
MY_PART_TBL                    P_900                64          1
MY_PART_TBL                    P_1000               64          1
MY_PART_TBL                    P_MAX                64          1
MY_PART_TBL_NDX                P_100             18432         33
MY_PART_TBL_NDX                P_200             19456         34
MY_PART_TBL_NDX                P_300                64          1
MY_PART_TBL_NDX                P_400                64          1
MY_PART_TBL_NDX                P_600                64          1
MY_PART_TBL_NDX                P_680                64          1
MY_PART_TBL_NDX                P_700                64          1
MY_PART_TBL_NDX                P_800                64          1
MY_PART_TBL_NDX                P_900                64          1
MY_PART_TBL_NDX                P_1000               64          1
MY_PART_TBL_NDX                P_MAX                64          1

22 rows selected.

SQL> 

(Note : I have manually relocated Partition P_1000 in the listing).
Partitions P_600, P_680 and P_700 had been created by SPLIT PARTITION commands in the previous post, beginning with segment-created P_MAX partition.  However, after rebuilding P_MAX to 64KB Extents, subsequently SPLITted Partitions (P_800 to P_1000) are also 64KB.

Note : I am not advising that all have to Partitions be 64K.  (Observe how AutoAllocate did allocate 1MB Extents to P_100 and P_200 after the first 1MB of space usage (using 16 64KB Extents).
.
.
.