Have you ever wondered about how Partitions are mapped to Segments ? How does Oracle identify the "data object" (as different from the logical object) that maps to the Segment for an Object ?
[Why does Oracle differentiate between "object_id" and "data_object_id" ? An object may be created without a Segment. An existing Segment for an object (e.g. a Table) may be recreated (e.g. by an ALTER TABLE tablename MOVE command) thus changing it's "data_object_id" without changing its "object_id")].
For a Partitioned Table, every Partition is an Object. But (beginning with 11.2.0.2 and "deferred_segment_creation" behaviour), the Segment is created only when the Partition is populated with one or more rows. What happens when a Partition is SPLIT ?
Here is a simple demonstration with some notes :
Notice how, although there are 6 Partitions and 6 Objects, there are only 4 Segments. Only the first 4 Partitions that have rows in them now have Segments associated with them. Pay attention to the (HEADER_FILE, HEADER_BLOCK) and DATA_OJECT_ID values as I proceed to manipulate the Partitions.
Notice how Partition P_50, with a new OBJECT_ID and DATA_OBJECT_ID (above the highest then-existent value). But did you notice that the (HEADER_FILE, HEADER_BLOCK) pair and the DATA_OBJECT_ID for Partition P_100 are completely new values ? Oracle has created a *new* Segment for Partition P_100 and discarded the old segment. The SPLIT operation has created two *new* Segments and removed the old Segment for Partition P_100. What does this also mean ? Oracle had to actually rewrite all 100 rows in that Partition in the process of creating two new Segments. Let me say that again : Oracle had to rewrite all 100 rows.
Let me proceed with another test.
Inserting a row into Partition P_500 has created a Segment (which did not exist earlier). [Coincidentally, it has actually "reused" the one extent that earlier belonged to Partition P_100 -- look at the (HEADER_FILE, HEADER_BLOCK) pair -- and had become a free extent for a while. I say "coincidentally" because if there had been some other extent allocation for this Table or any other Table or Index in the same tablespace, that free extent could have been reused by another Partition / Table / Index]. The DATA_OBJECT_ID had already been allocated to the Partition when the Table was created, so this doesn't change.
I have deliberately inserted a "boundary" value of 450 in this Partition. This will be the maxvalue for Partition P_450. I will now proceed to split the Partition along this boundary.
Now, isn't that interesting ? Partition P_450 has "inherited" the (HEADER_FILE, HEADER_BLOCK) and DATA_OBJECT_ID of what was Partition P_500 earlier. What has happened is that the Segment for Partition P_500 has now become the Segment for Partition P_450 while a *new* Segment (and DATA_OBJECT_ID) has been created for Partition P_500. Effectively, the physical entity (Segment and Data_Object) for Partition P_500 has been "reused" for Partition P_450 while Partition P_500 has been "born again" in a new incarnation. This SPLIT (unlike the earlier SPLIT) resulted in only 1 new Segment (and Data_Object). The existing row remained in the existing Segment without being rewritten. The new Segment is created for any "empty" Partition.
For further reading, I suggest that you read on "fast split" operations under "Optimizing SPLIT PARTITION and SPLIT SUBPARTITION Operations".
There you can see that the row is in Partition P_450 whose physical extent is the same as before.
Note : In my demonstration, each Segment is only 1 Extent.
You may have to be dealing with Segments with multiple Extents.
Another takeaway from the query on DBA_SEGMENTS is that (OWNER, SEGMENT_NAME) is not the identifying Key for a Segment. In fact for a Partitioned table there is *NO* Segment for the Table itself. There exist Segments for the Table Partitions. The query on DBA_SEGMENTS must be on (OWNER, SEGMENT_NAME, PARTITION_NAME) by SEGMENT_TYPE = 'TABLE PARTITION'.
.
.
.
[Why does Oracle differentiate between "object_id" and "data_object_id" ? An object may be created without a Segment. An existing Segment for an object (e.g. a Table) may be recreated (e.g. by an ALTER TABLE tablename MOVE command) thus changing it's "data_object_id" without changing its "object_id")].
For a Partitioned Table, every Partition is an Object. But (beginning with 11.2.0.2 and "deferred_segment_creation" behaviour), the Segment is created only when the Partition is populated with one or more rows. What happens when a Partition is SPLIT ?
Here is a simple demonstration with some notes :
SQL> drop table test_partitioned_table purge; Table dropped. SQL> SQL> -- create test table with 5+1 partitions SQL> create table test_partitioned_table 2 ( 3 id_column number, 4 data_column varchar2(15) 5 ) 6 partition by range (id_column) 7 ( 8 partition p_100 values less than (101), 9 partition p_200 values less than (201), 10 partition p_300 values less than (301), 11 partition p_400 values less than (401), 12 partition p_500 values less than (501), 13 partition p_max values less than (MAXVALUE) 14 ) 15 / Table created. SQL> SQL> -- populate the first 4 partitions SQL> insert into test_partitioned_table 2 select rownum, to_char(rownum) 3 from dual 4 connect by level < 379 5 / 378 rows created. SQL> SQL> -- identify the segments that did get created SQL> -- note : Use DBA_SEGMENTS as HEADER_% information is not available in USER_SEGMENTS SQL> select partition_name, header_file, header_block 2 from dba_segments 3 where owner = 'HEMANT' 4 and segment_name = 'TEST_PARTITIONED_TABLE' 5 and segment_type = 'TABLE PARTITION' 6 order by partition_name 7 / PARTITION_NAME HEADER_FILE HEADER_BLOCK ------------------------------ ----------- ------------ P_100 11 34449 P_200 11 35473 P_300 11 36497 P_400 11 38417 SQL> SQL> -- identify the objects SQL> -- use the DBA_OBJECTS view for consistency with previous query on DBA_SEGMENTS SQL> select subobject_name, object_id, data_object_id 2 from dba_objects 3 where owner = 'HEMANT' 4 and object_name = 'TEST_PARTITIONED_TABLE' 5 and object_type = 'TABLE PARTITION' 6 order by subobject_name 7 / SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID ------------------------------ ---------- -------------- P_100 114541 114541 P_200 114542 114542 P_300 114543 114543 P_400 114544 114544 P_500 114545 114545 P_MAX 114546 114546 6 rows selected. SQL>
Notice how, although there are 6 Partitions and 6 Objects, there are only 4 Segments. Only the first 4 Partitions that have rows in them now have Segments associated with them. Pay attention to the (HEADER_FILE, HEADER_BLOCK) and DATA_OJECT_ID values as I proceed to manipulate the Partitions.
SQL> -- split the first partition
SQL> alter table test_partitioned_table
2 split partition p_100
3 at (51)
4 into (partition p_50, partition p_100)
5 /
Table altered.
SQL>
SQL> -- identify the segments
SQL> select partition_name, header_file, header_block
2 from dba_segments
3 where owner = 'HEMANT'
4 and segment_name = 'TEST_PARTITIONED_TABLE'
5 and segment_type = 'TABLE PARTITION'
6 and partition_name in ('P_50','P_100')
7 order by partition_name
8 /
PARTITION_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
P_100 11 40465
P_50 11 39441
SQL>
SQL> -- identify the objects
SQL> select subobject_name, object_id, data_object_id
2 from dba_objects
3 where owner = 'HEMANT'
4 and object_name = 'TEST_PARTITIONED_TABLE'
5 and object_type = 'TABLE PARTITION'
6 and subobject_name in ('P_50','P_100')
7 order by subobject_name
8 /
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
P_100 114541 114548
P_50 114547 114547
SQL>
Notice how Partition P_50, with a new OBJECT_ID and DATA_OBJECT_ID (above the highest then-existent value). But did you notice that the (HEADER_FILE, HEADER_BLOCK) pair and the DATA_OBJECT_ID for Partition P_100 are completely new values ? Oracle has created a *new* Segment for Partition P_100 and discarded the old segment. The SPLIT operation has created two *new* Segments and removed the old Segment for Partition P_100. What does this also mean ? Oracle had to actually rewrite all 100 rows in that Partition in the process of creating two new Segments. Let me say that again : Oracle had to rewrite all 100 rows.
Let me proceed with another test.
SQL> -- insert one row into the 5th partition SQL> insert into test_partitioned_table 2 select 450, to_char(450) from dual 3 / 1 row created. SQL> SQL> -- identify the segment SQL> select partition_name, header_file, header_block 2 from dba_segments 3 where owner = 'HEMANT' 4 and segment_name = 'TEST_PARTITIONED_TABLE' 5 and segment_type = 'TABLE PARTITION' 6 and partition_name = 'P_500' 7 order by partition_name 8 / PARTITION_NAME HEADER_FILE HEADER_BLOCK ------------------------------ ----------- ------------ P_500 11 34449 SQL> SQL> -- identify the object SQL> select subobject_name, object_id, data_object_id 2 from dba_objects 3 where owner = 'HEMANT' 4 and object_name = 'TEST_PARTITIONED_TABLE' 5 and object_type = 'TABLE PARTITION' 6 and subobject_name = 'P_500' 7 order by subobject_name 8 / SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID ------------------------------ ---------- -------------- P_500 114545 114545 SQL>
Inserting a row into Partition P_500 has created a Segment (which did not exist earlier). [Coincidentally, it has actually "reused" the one extent that earlier belonged to Partition P_100 -- look at the (HEADER_FILE, HEADER_BLOCK) pair -- and had become a free extent for a while. I say "coincidentally" because if there had been some other extent allocation for this Table or any other Table or Index in the same tablespace, that free extent could have been reused by another Partition / Table / Index]. The DATA_OBJECT_ID had already been allocated to the Partition when the Table was created, so this doesn't change.
I have deliberately inserted a "boundary" value of 450 in this Partition. This will be the maxvalue for Partition P_450. I will now proceed to split the Partition along this boundary.
SQL> -- split the 5th partition
SQL> -- now p_450 will have the 1 row and p_500 no rows
SQL> alter table test_partitioned_table
2 split partition p_500
3 at (451)
4 into (partition p_450, partition p_500)
5 /
Table altered.
SQL>
SQL> -- identify the segments
SQL> select partition_name, header_file, header_block
2 from dba_segments
3 where owner = 'HEMANT'
4 and segment_name = 'TEST_PARTITIONED_TABLE'
5 and segment_type = 'TABLE PARTITION'
6 and partition_name in ('P_450','P_500')
7 order by partition_name
8 /
PARTITION_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
P_450 11 34449
P_500 11 41489
SQL>
SQL> -- identify the objects
SQL> select subobject_name, object_id, data_object_id
2 from dba_objects
3 where owner = 'HEMANT'
4 and object_name = 'TEST_PARTITIONED_TABLE'
5 and object_type = 'TABLE PARTITION'
6 and subobject_name in ('P_450','P_500')
7 order by subobject_name
8 /
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
P_450 114549 114545
P_500 114545 114550
SQL>
Now, isn't that interesting ? Partition P_450 has "inherited" the (HEADER_FILE, HEADER_BLOCK) and DATA_OBJECT_ID of what was Partition P_500 earlier. What has happened is that the Segment for Partition P_500 has now become the Segment for Partition P_450 while a *new* Segment (and DATA_OBJECT_ID) has been created for Partition P_500. Effectively, the physical entity (Segment and Data_Object) for Partition P_500 has been "reused" for Partition P_450 while Partition P_500 has been "born again" in a new incarnation. This SPLIT (unlike the earlier SPLIT) resulted in only 1 new Segment (and Data_Object). The existing row remained in the existing Segment without being rewritten. The new Segment is created for any "empty" Partition.
For further reading, I suggest that you read on "fast split" operations under "Optimizing SPLIT PARTITION and SPLIT SUBPARTITION Operations".
SQL> select * from test_partitioned_table partition (p_450);
ID_COLUMN DATA_COLUMN
---------- ---------------
450 450
SQL> select * from test_partitioned_table partition (p_500);
no rows selected
SQL>
There you can see that the row is in Partition P_450 whose physical extent is the same as before.
Note : In my demonstration, each Segment is only 1 Extent.
SQL> l 1 select partition_name, extents 2 from dba_segments 3 where owner = 'HEMANT' 4 and segment_name = 'TEST_PARTITIONED_TABLE' 5 and segment_type = 'TABLE PARTITION' 6* order by partition_name SQL> / PARTITION_NAME EXTENTS ------------------------------ ---------- P_100 1 P_200 1 P_300 1 P_400 1 P_450 1 P_50 1 P_500 1 7 rows selected. SQL>
You may have to be dealing with Segments with multiple Extents.
Another takeaway from the query on DBA_SEGMENTS is that (OWNER, SEGMENT_NAME) is not the identifying Key for a Segment. In fact for a Partitioned table there is *NO* Segment for the Table itself. There exist Segments for the Table Partitions. The query on DBA_SEGMENTS must be on (OWNER, SEGMENT_NAME, PARTITION_NAME) by SEGMENT_TYPE = 'TABLE PARTITION'.
.
.
.
No comments:
Post a Comment