Search My Oracle Blog

Custom Search

18 May, 2016

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.
,
,
,

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016