Search My Oracle Blog

Custom Search

23 April, 2016

Partition Storage -- 3 : Adding new Range Partitions with SPLIT

Building on the Partitioned Table in the previous two blog posts...

We know that the Table is a Range Partitioned Table.  With a MAXVALUE Partition, the only way to add new Partitions is to use the SPLIT PARTITION command.

First, let's review the Table, Partitions and Segments.

SQL> select table_name, num_rows
  2  from user_tables
  3  where table_name = 'MY_PART_TBL' 
  4  /

TABLE_NAME    NUM_ROWS
---------------- ----------
MY_PART_TBL     2200004

SQL> select partition_name, num_rows, blocks
  2  from user_tab_partitions
  3  where table_name = 'MY_PART_TBL'
  4  order by 1
  5  /

PARTITION_NA   NUM_ROWS     BLOCKS
------------ ---------- ----------
P_100  1100001       3022
P_200  1100001       3668
P_300        1       1006
P_400        1       1006
P_MAX        0   0

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

SEGMENT_NAME      SEGMENT_TYPE PARTITION_NA BYTES/1024    EXTENTS
-------------------- ------------------ ------------ ---------- ----------
MY_PART_TBL      TABLE PARTITION P_100    24576   3
MY_PART_TBL      TABLE PARTITION P_200    32768   4
MY_PART_TBL      TABLE PARTITION P_300     8192   1
MY_PART_TBL      TABLE PARTITION P_400     8192   1
MY_PART_TBL_NDX      INDEX PARTITION P_100    28672  43
MY_PART_TBL_NDX      INDEX PARTITION P_200    33792  48
MY_PART_TBL_NDX      INDEX PARTITION P_300       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_400       64   1

8 rows selected.

SQL> 


So, the table has 5 partitions P_100 to P_MAX but only 4 have segments created after one or more rows have been populated.  P_MAX has no segment created for either the Table Partition or the Index Partition.

What happens if we SPLIT P_MAX (an empty, segmentless Partition) to create a new Partition ?

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

Table altered.

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

PL/SQL procedure successfully completed.

SQL> select partition_name, high_value, num_rows, blocks
  2  from user_tab_partitions
  3  where table_name = 'MY_PART_TBL'
  4  order by partition_position
  5  /

PARTITION_NA HIGH_VALUE  NUM_ROWS     BLOCKS
------------ ---------------- ---------- ----------
P_100      101   1100001       3022
P_200      201   1100001       3668
P_300      301         1       1006
P_400      401         1       1006
P_500      501         0   0
P_MAX      MAXVALUE         0   0

6 rows selected.

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

SEGMENT_NAME      SEGMENT_TYPE PARTITION_NA BYTES/1024    EXTENTS
-------------------- ------------------ ------------ ---------- ----------
MY_PART_TBL      TABLE PARTITION P_100    24576   3
MY_PART_TBL      TABLE PARTITION P_200    32768   4
MY_PART_TBL      TABLE PARTITION P_300     8192   1
MY_PART_TBL      TABLE PARTITION P_400     8192   1
MY_PART_TBL_NDX      INDEX PARTITION P_100    28672  43
MY_PART_TBL_NDX      INDEX PARTITION P_200    33792  48
MY_PART_TBL_NDX      INDEX PARTITION P_300       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_400       64   1

8 rows selected.

SQL> 


So, the process of creating Partition P_500 did not create a segment for it, because P_MAX which it was SPLIT from, was segmentless.  What happens if I split a Partition with 1 or more rows ?

SQL> insert into my_part_tbl
  2  select 550, 'Five Hundred Fifty'
  3  from dual 
  4  /

1 row created.

SQL> commit;
SQL> select segment_name, segment_type, partition_name, bytes/1024, extents
  2  from user_segments
  3  where segment_name like 'MY_PART_%'
  4  order by 1,2,3
  5  /

SEGMENT_NAME      SEGMENT_TYPE PARTITION_NA BYTES/1024    EXTENTS
-------------------- ------------------ ------------ ---------- ----------
MY_PART_TBL      TABLE PARTITION P_100    24576   3
MY_PART_TBL      TABLE PARTITION P_200    32768   4
MY_PART_TBL      TABLE PARTITION P_300     8192   1
MY_PART_TBL      TABLE PARTITION P_400     8192   1
MY_PART_TBL      TABLE PARTITION P_MAX     8192   1
MY_PART_TBL_NDX      INDEX PARTITION P_100    28672  43
MY_PART_TBL_NDX      INDEX PARTITION P_200    33792  48
MY_PART_TBL_NDX      INDEX PARTITION P_300       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_400       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_MAX       64   1

10 rows selected.

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

Table altered.

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

SEGMENT_NAME      SEGMENT_TYPE PARTITION_NA BYTES/1024    EXTENTS
-------------------- ------------------ ------------ ---------- ----------
MY_PART_TBL      TABLE PARTITION P_100    24576   3
MY_PART_TBL      TABLE PARTITION P_200    32768   4
MY_PART_TBL      TABLE PARTITION P_300     8192   1
MY_PART_TBL      TABLE PARTITION P_400     8192   1
MY_PART_TBL      TABLE PARTITION P_600     8192   1
MY_PART_TBL      TABLE PARTITION P_MAX     8192   1
MY_PART_TBL_NDX      INDEX PARTITION P_100    28672  43
MY_PART_TBL_NDX      INDEX PARTITION P_200    33792  48
MY_PART_TBL_NDX      INDEX PARTITION P_300       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_400       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_600       64   1

SEGMENT_NAME      SEGMENT_TYPE PARTITION_NA BYTES/1024    EXTENTS
-------------------- ------------------ ------------ ---------- ----------
MY_PART_TBL_NDX      INDEX PARTITION P_MAX       64   1

12 rows selected.

SQL> 


So, the row for ID_COLUMN=550 created the segment for Partition P_MAX. Subsequently, SPLITting this Partition into P_600 and P_MAX resulted into two Partitions of 8MB each.
The row for ID_COLUMN=550 would be in the P_600 Partition and the P_MAX Partition would now be the empty Partition.  Yet, even P_MAX now takes an 8MB extent, unlike earlier.

Let's try doing such a SPLIT that, say P_700 is created empty but P_MAX inherits the row.

SQL> insert into my_part_tbl
  2  select 900, 'Nine Hundred'
  3  from dual
  4  /

1 row created.

SQL> commit;

Commit complete.

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

Table altered.

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

SEGMENT_NAME      SEGMENT_TYPE PARTITION_NA BYTES/1024    EXTENTS
-------------------- ------------------ ------------ ---------- ----------
MY_PART_TBL      TABLE PARTITION P_100    24576   3
MY_PART_TBL      TABLE PARTITION P_200    32768   4
MY_PART_TBL      TABLE PARTITION P_300     8192   1
MY_PART_TBL      TABLE PARTITION P_400     8192   1
MY_PART_TBL      TABLE PARTITION P_600     8192   1
MY_PART_TBL      TABLE PARTITION P_700     8192   1
MY_PART_TBL      TABLE PARTITION P_MAX     8192   1
MY_PART_TBL_NDX      INDEX PARTITION P_100    28672  43
MY_PART_TBL_NDX      INDEX PARTITION P_200    33792  48
MY_PART_TBL_NDX      INDEX PARTITION P_300       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_400       64   1

SEGMENT_NAME      SEGMENT_TYPE PARTITION_NA BYTES/1024    EXTENTS
-------------------- ------------------ ------------ ---------- ----------
MY_PART_TBL_NDX      INDEX PARTITION P_600       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_700       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_MAX       64   1

14 rows selected.

SQL> select count(*) from my_part_tbl partition (P_700);

  COUNT(*)
----------
  0

SQL> 


Again, both Partitions (P_700 and P_MAX) have a segment of 8MB.
This means that, once a Segment for a Partition is created, any SPLIT of that Partition results into two Segments inheriting the same 8MB Extent Size, irrespective of the fact that one of the two may be empty.

SQL> alter table my_part_tbl
  2  split partition p_700
  3  at (681)
  4  into (partition p_680, partition p_700)
  5  /

Table altered.

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

SEGMENT_NAME      SEGMENT_TYPE PARTITION_NA BYTES/1024    EXTENTS
-------------------- ------------------ ------------ ---------- ----------
MY_PART_TBL      TABLE PARTITION P_100    24576   3
MY_PART_TBL      TABLE PARTITION P_200    32768   4
MY_PART_TBL      TABLE PARTITION P_300     8192   1
MY_PART_TBL      TABLE PARTITION P_400     8192   1
MY_PART_TBL      TABLE PARTITION P_600     8192   1
MY_PART_TBL      TABLE PARTITION P_680     8192   1
MY_PART_TBL      TABLE PARTITION P_700     8192   1
MY_PART_TBL      TABLE PARTITION P_MAX     8192   1
MY_PART_TBL_NDX      INDEX PARTITION P_100    28672  43
MY_PART_TBL_NDX      INDEX PARTITION P_200    33792  48
MY_PART_TBL_NDX      INDEX PARTITION P_300       64   1

SEGMENT_NAME      SEGMENT_TYPE PARTITION_NA BYTES/1024    EXTENTS
-------------------- ------------------ ------------ ---------- ----------
MY_PART_TBL_NDX      INDEX PARTITION P_400       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_600       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_680       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_700       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_MAX       64   1

16 rows selected.

SQL> 


That is confirmation that SPLITting a Partition that has a segment (even if it is empty) results into two segmented partitions, even if both are empty.

Going back to Parttion P_500 (which is present but segmentless), what happens if we split it ?

SQL> alter table my_part_tbl
  2  split partition p_500
  3  at (451)
  4  into (partition p_450, partition p_500)
  5  /

Table altered.

SQL>    
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'MY_PART_TBL'
  4  order by partition_position
  5  /

PARTITION_NA HIGH_VALUE
------------ ----------------
P_100      101
P_200      201
P_300      301
P_400      401
P_450      451
P_500      501
P_600      601
P_680      681
P_700      701
P_MAX      MAXVALUE

10 rows selected.

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

SEGMENT_NAME      SEGMENT_TYPE PARTITION_NA BYTES/1024    EXTENTS
-------------------- ------------------ ------------ ---------- ----------
MY_PART_TBL      TABLE PARTITION P_100    24576   3
MY_PART_TBL      TABLE PARTITION P_200    32768   4
MY_PART_TBL      TABLE PARTITION P_300     8192   1
MY_PART_TBL      TABLE PARTITION P_400     8192   1
MY_PART_TBL      TABLE PARTITION P_600     8192   1
MY_PART_TBL      TABLE PARTITION P_680     8192   1
MY_PART_TBL      TABLE PARTITION P_700     8192   1
MY_PART_TBL      TABLE PARTITION P_MAX     8192   1
MY_PART_TBL_NDX      INDEX PARTITION P_100    28672  43
MY_PART_TBL_NDX      INDEX PARTITION P_200    33792  48
MY_PART_TBL_NDX      INDEX PARTITION P_300       64   1

SEGMENT_NAME      SEGMENT_TYPE PARTITION_NA BYTES/1024    EXTENTS
-------------------- ------------------ ------------ ---------- ----------
MY_PART_TBL_NDX      INDEX PARTITION P_400       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_600       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_680       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_700       64   1
MY_PART_TBL_NDX      INDEX PARTITION P_MAX       64   1

16 rows selected.

SQL> 


Splitting segmentless Partition P_500 into P_450 and P_500 did *not* result into new Segments.

 This has implications for your SPLIT Partition strategy.  If you need to do a recursive split to create, say, 90 1-day Partitions and you start with a Partition that has a segment (even if empty), you get 90 new segments as well.  Thus, the table would suddenly "grow" by 720MB without having inserted a single row on the day you create these 90 Partitions.  You may get some questions from IT Operations / Support about the sudden "growth" in 1 day.
On the other hand, starting with a segmentess Partition, you get 90 new segmentless Partitions.  Their segments will be created when they are populated.
.
.

.

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