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.
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 ?
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 ?
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.
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.
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 ?
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.
.
.
.
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:
Post a Comment