19 May, 2014

Partitions and Segments and Data Objects

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 :


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: