Partition Storage -- 8 : Manually Sizing Partitions

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.

SQL> create table my_part_tbl_init_sized (id_column number(6), data_column varchar2(100))
  2  partition by range (id_column)
  3  (partition p_100 values less than (101) segment creation immediate                                
  4                                          storage (initial 64K next 64K) tablespace hemant,
  5   partition p_200 values less than (201) segment creation immediate
  6                                          storage (initial 1M next 1M) tablespace hemant,
  7   partition p_max values less than (maxvalue) segment creation immediate
  8                                          storage (initial 8M next 1M) tablespace hemant)
  9  /

Table created.

SQL> 
SQL> select partition_name, initial_extent, next_extent, tablespace_name
  2  from user_tab_partitions
  3  where table_name = upper('my_part_tbl_init_sized')
  4  order by partition_position
  5  /

PARTITION_NA INITIAL_EXTENT NEXT_EXTENT TABLESPACE_NAME
------------ -------------- ----------- ------------------------------
P_100                 65536       65536 HEMANT
P_200               1048576     1048576 HEMANT
P_MAX               8388608     1048576 HEMANT

SQL> 
SQL> select partition_name, bytes/1024, extents
  2  from user_segments
  3  where segment_name = upper('my_part_tbl_init_sized')
  4  and segment_type = 'TABLE PARTITION'
  5  order by 1
  6  /

PARTITION_NA BYTES/1024    EXTENTS
------------ ---------- ----------
P_100                64          1
P_200               1024         1
P_MAX               8192         1

SQL> 

However, the caveat about Partition Extent Sizes when SPLIT is used as shown in the third post still applies.

SQL> alter table my_part_tbl_init_sized
  2  split partition p_max
  3  at (301)
  4  into (partition p_300, partition p_max)
  5  /

Table altered.

SQL> 
SQL> select partition_name, initial_extent, next_extent, tablespace_name
  2  from user_tab_partitions
  3  where table_name = upper('my_part_tbl_init_sized')
  4  order by partition_position
  5  /

PARTITION_NA INITIAL_EXTENT NEXT_EXTENT TABLESPACE_NAME
------------ -------------- ----------- ------------------------------
P_100                 65536       65536 HEMANT
P_200               1048576     1048576 HEMANT
P_300               8388608     1048576 HEMANT
P_MAX               8388608     1048576 HEMANT

SQL> 
SQL> select partition_name, bytes/1024, extents
  2  from user_segments
  3  where segment_name = upper('my_part_tbl_init_sized')
  4  and segment_type = 'TABLE PARTITION'
  5  order by 1
  6  /

PARTITION_NA BYTES/1024    EXTENTS
------------ ---------- ----------
P_100                64          1
P_200              1024          1
P_300              8192          1
P_MAX              8192          1

SQL> 

So, SPLITting P_MAX to create P_300 created P_300 with an 8MB extent because the sizing is inherited from P_MAX.  How do I change the Partition Extent sizes on SPLIT ?

SQL> alter table my_part_tbl_init_sized
  2  split partition p_max
  3  at (501)
  4  into (partition p_500 storage (initial 64K next 64K) tablespace hemant, partition p_max)
  5  /

Table altered.

SQL> 
SQL> alter table my_part_tbl_init_sized
  2  split partition p_500
  3  at (401)
  4  into (partition p_400, partition p_500)
  5  /

Table altered.

SQL> 
SQL> select partition_name, initial_extent, next_extent, tablespace_name
  2  from user_tab_partitions
  3  where table_name = upper('my_part_tbl_init_sized')
  4  order by partition_position
  5  /

PARTITION_NA INITIAL_EXTENT NEXT_EXTENT TABLESPACE_NAME
------------ -------------- ----------- ------------------------------
P_100                 65536       65536 HEMANT
P_200               1048576     1048576 HEMANT
P_300               8388608     1048576 HEMANT
P_400                 65536       65536 HEMANT
P_500                 65536       65536 HEMANT
P_MAX               8388608     1048576 HEMANT

6 rows selected.

SQL> select partition_name, bytes/1024, extents
  2  from user_segments
  3  where segment_name = upper('my_part_tbl_init_sized')
  4  and segment_type = 'TABLE PARTITION'
  5  order by 1
  6  /

PARTITION_NA BYTES/1024    EXTENTS
------------ ---------- ----------
P_100               64           1
P_200             1024           1
P_300             8192           1
P_400               64           1
P_500               64           1
P_MAX             8192           1

6 rows selected.

SQL> 

This time, when I split P_MAX I deliberately specified a size for P_500.  Then, when I split P_500 further, the new P_400 inherited the sizing from P_500.
,
,
,