7. Partitioned Objects#
What is Partitioning?#
Partitioning is the division of a large database object into several small pieces for easier management.
A large database object that has been partitioned is called a "partitioned object", and each piece of a partitioned object is called a "partition".
Partitioned Objects and Non-Partitioned Objects#
When an end-user accesses a partitioned object, the user cannot perceive any difference from a non-partitioned object. That is, from the user's perspective, both partitioned and non-partitioned objects are recognized as database objects, and it is not apparent whether a particular object has been partitioned. This allows the user to execute queries or DML statements (i.e. insert, delete and update records) in the same way regardless of the partition of the object.
The differences between partitioned objects and non-partitioned objects in terms of database structure are as follows.
Internal Structure#
A non-partitioned object is an object that depends on one table space. A nonpartitioned object is stored in only one tablespace.
A partitioned object can be stored across multiple tablespaces. This is illustrated in [Figure 7-1].
A partitioned object internally consists of multiple partitions. Each partition has the same constraints as nonpartitioned objects, and one partition only depends on a single tablespace.
A partitioned object causes partitions distributed across multiple tablespaces to appear as a single object. [Figure 7-2] shows the internal structure of a partitioned object.
Advantages of Partitioned Objects#
Due to this structure characteristics, a partitioned object has the following advantages:
- Faster data loading and index rebuilding
- Faster partial deletes
- Faster table and index scans
- More flexible response to disk failures
Partition Key#
A partition key is a basis on which tables are partitioned. A partition key consists of one or more of the columns of the table to be partitioned. These columns are called partition key columns.
A partition key column must have a data type that supports size comparisons (i.e. <, >, =). Only such columns can be used as partition key columns.
For example, when a record is inserting a record, it must be clear on which partition will hold the inserted record. In order to satisfy this requirement, it must be possible to compare partition key columns according to the relevant conditions unambiguously. Therefore, non-comparable types such as BINARY, GEOMETRY, BLOB, and CLOB cannot be partitioning key columns.
Partitioned Objects#
Tables and indexes are the database objects that can be partitioned.
When a table is partitioned, the table is called a "partitioned table", and when an index is partitioned, it is called a "partitioned index". These partitioned tables and partitioned indexes are collectively known as "partitioned objects".
A partitioned object must satisfy the following rule:
non-partitioned_object ≡ ∑partition ………………… rule1
The above rule means that a non-partitioned object must be equal to the sum of its partitions if it were partitioned. In other words, it is not possible to partition only part of a non-partitioned object.
Partitioned Tables#
The term "partitioned table" refers to a large table that has been partitioned into multiple partitions based on partitioning conditions (range, list, or hash). This is illustrated in [Figure 7-3].
In [Figure 7-3], a non-partitioned table is partitioned based on color, resulting in a partitioned table with three partitions.
From a structural point of view, the database object that conceptually bears the closest resemblance to a partitioned table is a union view. A union view allows multiple tables to be treated as a single object, but does not itself take up any physical space. Likewise, the partitions in a partitioned table occupy physical space, but the partitioned table itself does not.
However, there are some characteristics of partitioned tables that make them different from union views.
Updatability#
Partitioned tables can be updated. In contrast, a union for union views, records can be updated by accessing individual tables, but not by accessing the union view.
Index Range#
Indexes can be built for partitioned tables. In contrast, indexes can be built for the individual tables that make up union views, but not for the union views themselves.
A partitioned table can thus be thought of as a union view that can be accessed to update records, and for which an index can be built.
Partitioned tables can be classified as follows based on the type of storage medium on which the partitions are saved.
Partitioned Memory Table#
A partitioned table for which all partitions are stored in a memory tablespace.
Partitioned Disk Table#
A partitioned table for which all partitions are stored in a disk tablespace.
Partitioned Indexes#
Indexes for partitioned tables can be classified according to the following:
Whether they are partitioned:#
Partitioned indexes vs Non-partitioned indexes
The relationship between table and index:#
Global indexes vs Local indexes
Partitioned Indexes vs Non-Partitioned Indexes#
An index is classified either as a partitioned index or as a non-partitioned index, depending on whether it is partitioned.
The term "non-partitioned index" refers to an index that is not partitioned, whereas the term "partitioned index" refers to a large index that, just like a partitioned table, is divided into multiple partitions according to some partitioning criteria. This is illustrated in [Figure 7-4].
[Figure 7-4] shows a non-partitioned index that has been partitioned based on color, resulting in a partitioned index that consists of three partitions.
Partitioned indexes, which are partitioned according to some partition conditions, are classified as either prefixed indexes or non-prefixed indexes based on the relationship between an index partition key and an index key.
Prefixed Index#
In a prefixed index, the first column of an index key and the first column of an index partition key are the same column.
Non-prefixed Index#
In a non-prefixed index, the first column of an index key and the first column of an index partition key are not the same column.
[Figure 7-5] shows the difference between a prefixed index and a non-prefixed index, using a table that consists of the "sales_id" and "sales_date" columns as an example.
In the figure above, the indexes are partitioned on the basis of the "sales_date" column. Each index is classified as either a prefixed index or a non-prefixed index based on the key on which it was built.
In the figure, the prefixed index has the key "sales_date". In other words, the index partition key and the index key are based on the same column. This type of index is called a "prefixed index". In contrast, the non-prefixed index in the figure is sorted by "sales_id". Because this type of index is sorted according to a key other than the index partition key, it is called a "non-prefixed index"
The reason for distinguishing between prefixed and non-prefixed indexes is related to the UNIQUE attribute. Because the index key of a prefixed index is the same as its index partition key, the Altibase server can perform unique checks without searching all partitions in the partitioned index. However, in the case of non-prefixed indexes, the Altibase server must search all partitions included in the partitioned index. [Figure 7-6] displays an example of this:
In the example shown in [Figure 7-6], suppose that the index partition key of the table is the sales_date column, and that duplicate values are not allowed for the sales_id column. If a non-prefixed index ("IDX_NON_PREFIX") is built using the sales_id column as the index key, consider whether a unique check can be performed using IDX_NON_PREFIX when records are inserted using the following SQL statement:
INSERT INTO tbl_sales VALUES(9, 1월);
Because the value in the sales_date column of the record to be inserted is "January", a key will be inserted into IDX_PART_1. Even though a key value of 9 is present in the sales_id column in the IDX_PART_2 partition, the same value will be inserted without error into the IDX_PART_1 partition. Therefore, it is necessary to check all of the indexes when performing a unique check using a non-prefixed index.
Global Index and Local Index#
An index is classified as either a global index or a local index based on the relationship between a table partition key and an index partition key. The term "global index" refers to an index in which the index partition key and the table partition key are different (index_partition_key != table_partition_key), whereas the term "local index" refers to an index in which the index partition key and the table partition key are the same (index_partition_key == table_partition_key).
[Figure 7-7] shows the difference between a global index and a local index with reference by way of example to a table that consists of the sales_id and sales_date columns. In the figure, the indexes are sorted by sales_date, and each index is categorized as either a local or global index based on which index partition key is used to partition it.
In the figure, a local index is partitioned into three partitions on the basis of the sales_date column. A partitioned index in which the index partition key (sales_date) and the table partition key (sales_date) are the same is called a local index.
In contrast, at the bottom of the figure, a global index is partitioned on the basis of the sales_id column. That is, the index partition key (sales_id) and the table partition key (sales_date) are not the same. This type of partitioned index is called a global index
The reason that indexes are classified as global or local indexes is that they have different characteristics based on whether or not the table partition key and the index partition key are the same.
Building a partitioned index on the basis of a column other than the table partition key means that the keys in an index partition of a global index will point to several different table partitions. The result of this is that the use of a table modification statement (e.g. ALTER TABLE ....MERGE ...) on a partitioned table can cause the global index to be rebuilt.
In contrast, for a local index, because a partitioned table modification statement only changes the local index of the partitions, overall consistency does not suffer.
Global Non-partitioned Index#
Global non-partitioned indexes work similarly to non-partitioned indexes. These indexes are created globally without an index partition key on a partitioned table.
The following figure shows the creation of a global non-partitioned index without specifying an index partition key on the table tbl_sales, which is divided into three partitions.
Types of Indexes#
[Figure 7-9] shows the types of indexes that have been examined so far:
Altibase currently supports only local and global non-partitioned indexes. Global partitioned indexes are not supported. Please refer to the following table and figure:
[Table 7-1] Index Types Supported in Disk Tables
| Non-partitioned Table | Partitioned Table | |
|---|---|---|
| (Partitioned) Local Prefixed Index | X | O |
| (Partitioned)Local Non-prefixed Index | X | O |
| (Partitioned) Global Prefixed Index | X | X |
| (Partitioned) Global Non-prefixed Index | X | X |
| Global Non-partitioned Index | O | O |
Only global non-partitioned indexes can be built for non-partitioned tables and partitioned tables; local prefixed indexes and local non-prefixed indexes can be built for partitioned tables.
However, global non-partitioned indexes cannot be built for partitioned memory tables. The supported index types for memory tables are listed in the following table.
[Table 7-2] Index Types Supported in Memory Tables
| Non-partitioned Table | Partitioned Table | |
|---|---|---|
| (Partitioned) Local Prefixed Index | X | O |
| (Partitioned) Local Non-prefixed Index | X | O |
| (Partitioned) Global Prefixed Index | X | X |
| (Partitioned) Global Non-prefixed Index | X | X |
| Global Non-partitioned Index | O | X |
Hybrid Partitioned Table#
The partitioned table can be converted into another tablespace regardless of the storage space (e.g., disk/memory).
When converting from a disk partitioned table to memory/volatile partitioned table, even if the column is fixed or variable. It is treated as fixed.
However, the type of memory partitioned table stays the same even though it is converted into a disk partitioned table. Furthermore, the LOB column always becomes variable, but the data is stored as fixed or variable in accordance with the specified values in the IN ROW clause.
Restrictions#
- The global index does not support hybrid partitioned tables.
- The hybrid partitioned table cannot use Direct key Index, JOIN, and DISJOIN.
- ALTER TABLE MODIFY COLUMN cannot be used
Partitioned Conditions#
This section describes partition conditions and default partitions
Partition Pre-Conditions#
A partition condition refers to the criteria for partitioning. These criteria must comply with the following condition:
partition_conditioni ∩ partition_conditioni+1 = ∮…………rule2
The above rule means that there must not be any intersection between partition conditions for a partitioned table. If partition conditions intersect, it may not be clear in which partition a record is to be inserted. Therefore, if this rule is not satisfied when a partitioned object is created, the attempt to create the partitioned object will fail.
Additionally, partition conditions must always output the same value regardless of the circumstances. Assuming that a record is inserted into partition A at time t, the same record should be inserted into partition A, even if it is inserted at time t + 1. To satisfy this condition, the partition condition value described in the partition condition must always be a constant or deterministic built-in function. A determinable built-in function is a non-user-defined function provided by the system that returns the same value regardless of time.
The Default Partition#
In Altibase, partition conditions must always satisfy the following rule:
column_domain ≡ ∪partition_condition ……………… rule3
The above rule states that the entire domain of the partition key columns must equal the union of domains that satisfy each partition condition. This means that all partition conditions must be specified when a partitioned table is created.
In reality, however, Altibase provides the concept of a default partition because it is impossible for a user to specify all partition conditions.
[Figure 7-10] illustrates the concept of a default partition with reference by way of example to a partitioned object having three partitions. In the following statement, the user specifies partition conditions (partition_condition1 and partition_condition2) for partitions P1 and P2, and declares P3 as a default partition. Therefore, if a record to be inserted into this object satisfies neither partition_condition1 nor partition_condition2, it will be inserted into partition P3. In other words, the default partition is equivalent to the portion of the domain of a partition key column that remains after the domains satisfying the user-specified partition conditions have been subtracted from the entire domain.
The default partition must be specified when a partitioned object is created. If no default partition is specified, the attempt to create the partitioned object will fail.
Partitioning Methods#
Objects can be partitioned in four ways: range partitioning, list partitioning, hash partitioning and range partitioning using hash.
Range partitioning is a method of partitioning an object based on a range of partition key values. Range partitioning is suitable for data that are distributed across a linear range. In list partitioning, an object is partitioned based on sets of partition key values. List partitioning is useful with data that fall into discrete categories. In hash partitioning, an object is partitioned based on hash values that correspond to partition key values. Range partitioning using hash partitions by deciding the range according to the hash values that correspond to partition key values.
The following operations are supported on partitions created by each partitioning method:
[Table 7-3] Operations Supported for Partitions
| Operation | Partitions created by Range Partitioning | Partitions created by List Partitioning | Partitions created by Hash Partitioning | Partitions created by Range Partitioning using Hash |
|---|---|---|---|---|
| Alter | ○ | ○ | ○ | ○ |
| Add | △ (conditionally allowed) |
x | ○ | x |
| Coalesce | x | x | ○ | x |
| Drop | ○ | ○ | x | ○ |
| Merge | ○ | ○ | x | ○ |
| Rename | ○ | ○ | ○ | ○ |
| Split | ○ | ○ | x | ○ |
| Truncate | ○ | ○ | ○ | ○ |
Range Partitioning#
Range partitioning is commonly used with date data types in situations where it is necessary to manipulate historical data.
The only partition condition that is supported when defining a partition is 'LESS THAN'.
Default partitions can be defined using 'DEFAULT' clause and also can be omitted. Keep noted when creating a range partitioned table because default table cannot be deleted from the partitioned table with a default partition and default table cannot be added to the partitioned table without default partition.
The following is an example of range partitioning:
CREATE TABLE part_table
(
sales_date DATE,
sales_id NUMBER,
sales_city VARCHAR(20),
....
)
PARTITION BY RANGE(sales_date)
(
PARTITION part_1 VALUES LESS THAN ( TO_DATE('01-FEB-2006') ),
PARTITION part_2 VALUES LESS THAN ( TO_DATE('01-MAR-2006') ),
PARTITION part_3 VALUES LESS THAN ( TO_DATE('01-APR-2006') ),
PARTITION part_def VALUES DEFAULT
) TABLESPACE SYS_TBS_DISK_DATA;
In the example above, the part_table table is created and range-partitioned into 4 partitions. The first three partitions handle data values prior to the beginning of February, March, and April, respectively. The default partition part_def handles data that do not satisfy any of the conditions for the other partitions.
[Figure 7-11] shows the above example in graphical form:
Multi-Column Partitioning#
Multi-column partitioning is a method of partitioning an object using a partition key composed of multiple columns. Multi-column partitioning has the same concept as an index with multiple keys.
The following figure depicts a partition key constructed on the basis of two columns (i1, i2) in one dimension.
The following describes multi-column partitioning with reference to a SQL statement as an example:
CREATE TABLE part_table
(
sales_date DATE,
sales_id NUMBER,
sales_city VARCHAR(20),
....
)
PARTITION BY RANGE(sales_date, sales_id)
(
PARTITION part_1 VALUES LESS THAN ( TO_DATE('01-FEB-2006'), 200),
PARTITION part_2 VALUES LESS THAN ( TO_DATE('01-MAR-2006'), 100),
PARTITION part_3 VALUES LESS THAN ( TO_DATE('02-MAR-2006')),
PARTITION part_4 VALUES LESS THAN ( TO_DATE('01-APR-2006') ),
PARTITION part_def VALUES DEFAULT
) TABLESPACE SYS_TBS_DISK_DATA;
The above CREATE TABLE statement is illustrated below:
The following table shows the partition into which records will be inserted and the insertion conditions depending on the values of the records to be inserted.
[Table 7-4] Example of Multi-Column Based Partition Mapping.
| Value of the Record to be Inserted (sales_date, sales_id) | Partition into which Record Will be Inserted |
|---|---|
| TO_DATE('15-JAN-2006'), 100 | part_1 |
| TO_DATE('01-FEB-2006'), 100 | part_1 |
| TO_DATE('01-FEB-2006'), 200 | part_2 |
| TO_DATE('15-FEB-2006'), NULL | part_2 |
| TO_DATE('01-MAR-2006'), 50 | part_2 |
| TO_DATE('01-MAR-2006'), NULL | part_3 |
| TO_DATE('15-MAR-2006'), 200 | part_4 |
| NULL, 100 | part_def |
| NULL, NULL | part_def |
Operations on Range-Partitioned Objects#
There are 5 operations that can be performed on range-partitioned objects. These are: SPLIT PARTITION, DROP PARTITION, MERGE PARTITION, RENAME PARTITION and TRUNCATE PARTITION. Changing partition condition is currently not supported.
The process of adding a partition to a range-partitioned object is the same as the process of splitting partition conditions. Therefore, to add a partition, use 'Split Partition'.
Similarly, deleting a partition is the same as deleting partition conditions. Therefore, to delete a partition, use DROP PARTITION. When a partition is deleted, the deleted partition conditions become included in the partition conditions of a neighboring partition. Additionally, DROP PARTITION is distinguished from MERGE PARTITION based on whether or not records are deleted.
A partition can be renamed using RENAME PARTITION. To delete records from a partition, use TRUNCATE PARTITION; this deletes all records stored in the partition.
SPLIT PARTITION#
Split Partition is an operation in which a partition of a partitioned object is divided into two partitions. SPLIT PARTITION can be performed in one of two ways:
In-place Split#
With In-Place Split, some of the records are deleted from an existing partition and moved to a new partition; that is, the contents of the existing partition are changed.
In-Place Split is used when the name of one of the new partitions is the same as the name of the existing partition and the tablespace in which the new partitions are to be created is not specified. (Please refer to [Figure 7-14])
Out-Place Split#
With Out-Place Split, the contents of the existing partition are not changed. Instead, two new partitions are created, and then the records in the existing partition are divided and copied into them.
This method is used when the names of both of the new partitions are set differently from the name of the existing partition. Even if the name of one of the new partitions is the same as the name of the existing partition, this method is used when the tablespace in which the new partition having the same name of the existing partition is to be created is specified. (Refer to [Figure 7-15].)
The Out-Place and In-Place partition splitting methods described above differ from each other in various aspects related to performance and efficiency. When using In-Place Split, because the existing partition is the same as one of the new partitions, only one new partition is created. Therefore, In-Place Split is advantageous from the aspect of efficiency of the use of space.
Meanwhile, Out-Place Split consists of a process of creating two new partitions and inserting records into each of them. With In-Place Split, the operation that is performed on a record is a move operation, which consists of both an INSERT operation and a DELETE operation. In an MVCC environment, a DELETE operation has a more negative impact on system performance than an INSERT operation. Therefore, In-Place Split is more efficient when there is not enough storage space, whereas Out-Place Split realizes better performance in an MVCC environment when there is no shortage of storage space.
In the example shown in the above figure, part_2, which belongs to a partitioned object that originally consists of 4 partitions, is divided into part_2 and part_4.
① The new partition, part_4, is created, after which ② records are moved from part_2 to part_4 (move: INSERT & DELETE). Finally, ③ the conditions for part_2 are narrowed to the specified conditions.
In the example shown in the above figure, part_2, which belongs to a partitioned object that originally consists of 4 partitions, is divided into part_2 and part_4. ① The new partitions part_2 and part_4 are created, and ② records are inserted from the old part_2 into the new part_2 and part_4. Finally, ③ the old part_2 is physically deleted.
When the default partition is divided, the second partition in the INTO clause is automatically set as the default partition. This is because no command for specifying the default partition in the INTO subclause is supported.
DROP PARTITION#
Drop Partition is an operation in which a specific partition in a partitioned object is deleted. When a partition is deleted, all records and meta data in that partition are physically deleted. Furthermore, the conditions for that partition are incorporated in a neighboring partition.
The figure above illustrates an example in which a partition called part_2 is dropped from a partitioned object that consists of 4 partitions.
① The physical space (records and meta data) of part_2 is deleted, and ② its partition conditions are incorporated in the neighboring partition part_3, whose conditions are expanded to include the conditions of part_2
MERGE PARTITION#
MERGE PARTITION is an operation of merging two specified partitions among a partitioned object into one partition. The partitions to be merged must be neighboring partitions. The MERGE PARTITION operation can be conducted in one of two ways: In-Place Merge and Out-Place Merge.
In-place Merge#
As two existing partitions are merged into one of the partitions, the records from the other partition are inserted into it. This method is used when the names of the new partition and one of the existing partitions are the same and the tablespace in which the new partition is to be created is not specified. Please refer to [Figure 7-17].
Out-place Merge#
A new partition is created and the records in the existing partitions are copied into the new partition. This method is used when the name of the new partition is different from the names of the existing partitions. Additionally, even if the name of the new partition is the same as that of one of the existing partitions, this method is used if the tablespace in which the new partition is to be created is specified. Please refer to [Figure 7-18].
In-Place Merge and Out-Place Merge may differ from each other with respect to performance and efficiency. Because In-Place Merge does not create a new partition, but merely conducts an operation of INSERTing records, it is preferable to Out-place Merge from the aspect of performance.
In the example shown in the above figure, in a partitioned object comprising 4 partitions, the part_2 partition and the part_3 partition are merged into the original part_3 partition. ① The conditions for the existing part_3 are extended, and ② records from part_2 are inserted into part_3. Finally, ③ part_2 is physically deleted.
In [Figure 7-18], in a partitioned object comprising 4 partitions, the part_2 partition and the part_3 partition are merged into a newly created part_3 partition.
① The new part_3 partition is created, and ② records from part_2 and the old part_3 are inserted into the new part_3. Finally, ③ part_2 and the old part_3 are physically deleted.
RENAME PARTITION#
RENAME PARTITION changes the name of a partition without changing the partition conditions.
TRUNCATE PARTITION#
TRUNCATE PARTITION does not change the partition conditions but deletes all of the records stored in a partition.
List Partitioning#
List partitioning is a method of partitioning an object based on a set of partition key column values. List partitioning is commonly used when the range of partition key column values is not broad (e.g. January – December). List partitioning does not support multiple keys for partition key columns.
As with range partitioning, the DEFAULT clause is supported for creating basic partitions.
The following is an example of list partitioning:
CREATE TABLE part_table
(
sales_date DATE,
sales_id NUMBER,
sales_city VARCHAR(20),
....
)
PARTITION BY LIST(sales_city)
(
PARTITION part_1 VALUES ( 'SEOUL' , 'INCHEON' ),
PARTITION part_2 VALUES ( 'PUSAN' , 'JUNJU' ),
PARTITION part_3 VALUES ( 'CHUNGJU' , 'DAEJUN' ),
PARTITION part_def VALUES DEFAULT
) TABLESPACE SYS_TBS_DISK_DATA;
In the example above, the part_table table is created and list-partitioned so that it has 4 partitions. The first three partitions manage data by specific cities, while the default partition, part_def, handles data that do not meet any of the conditions specified for the other partitions. The example is illustrated below:
Operations on List-Partitioned Objects#
There are five types of operations that can be performed on list-partitioned objects. These are: SPLIT PARTITION, DROP PARTITION, MERGE PARTITION, RENAME PARTITION and TRUNCATE PARTITION. The SQL statements that are used are the same as for range-partitioned objects. Changing partition conditions are currently not supported.
SPLIT PARTITION#
As in range partitioning, in list partitioning, the SPLIT PARTITION operation can be conducted as either In-Place Split or Out-Place Split. When splitting a partition, if the name of one of the new partitions is the same as the name of the old partition, whether In-Place Split or Out-Place Split is used depends on whether a tablespace is specified.
In the example shown in the above figure, in a partitioned object with 4 partitions, the part_2 partition is divided into the part_2 partition and the part_4 partition. ① A new partition, part_4, is created, and ② records are moved from part_2 to part_4 (move: INSERT & DELETE). Finally, ③ the conditions for the part_2 partition are narrowed to the newly specified conditions ({'PUSAN', 'JUNJU'} -> {'JUNJU'}).
The sample shown in the figure above illustrates partitioning part_2 into part_2 and part_4 in a partitioned object with four partitions ① The new part_2 and part_4 partitions are created, and ② records from the old part_2 partition are inserted into the new part_2 partition and the part_4 partition. Finally, ③ the old part_2 partition is physically deleted.
DROP PARTITION#
Dropping a partition from a list-partitioned object is similar to dropping one from a range-partitioned object, except that the partition conditions of the partition to be dropped are incorporated as the conditions of the default partition rather than neighboring partition.
The example shown in the figure above illustrates dropping part_2 partition from a partitioned object with four partitions. ① The physical space (records and meta data) of the part_2 partition is dropped, and ② the conditions for part_2 are incorporated into those for the default partition part_def.
MERGE PARTITION#
As with range-partitioned objects, there are two ways to merge the partitions of a list-partitioned object: In-Place Merge and Out-Place Merge. If the name of the new partition is the same as that of one of the partitions being merged, whether In-Place Merge or Out-Place Merge is used depends on whether a tablespace is specified.
The example shown in the figure above illustrates the merging of part_2 and part_3 into part_3 (old) in a partitioned object with four partitions. ① The conditions for the existing part_3 are extended, and ② records from the part_2 partition are inserted into the part_3 partition. Finally, ③ the part_2 partition is physically deleted.
The example shown in the figure above illustrates the merging of part_2 and part_3 into part_3 (new) in a partitioned object with four partitions. ① The new partition part_3 is created, and ② records from the part_2 partition and the original part_3 partition are inserted into the new part_3 partition. Finally, ③ the part_2 partition and the original part_3 partition are physically deleted
RENAME PARTITION#
RENAME PARTITION changes the name of a partition without changing the partition conditions.
TRUNCATE PARTITION#
This deletes all of the records from a partition without changing the partition conditions.
Hash Partitioning#
Hash partitioning is a method of partitioning an object based on a hash value of a partition key. The partition key can consist of multiple columns. Hash partitioning is typically used for uniform load distribution rather than for manageability
Due to the characteristics of hash functions, hash partitioning is limited to general partition operations. Unlike range partitions and list partitions, SPLIT PARTITION, DROP PARTITION, and MERGE PARTITION cannot be performed on hash partitions; however, operations such as ADD PARTITION and COALESCE PARTITION are supported.
Unlike range partitioning and list partitioning, there is no default partition in hash partitioning. This is because the hash function can accept any value of the partition key. The location where records having NULL partition key values are inserted depends on the hash value for the NULL data. Although the hash value corresponding to NULL data is constant, different values can be output depending on the data type. The location in which records having NULL partition key values are stored can be different depending on the column type.
The following is an example of hash partitioning:
CREATE TABLE part_table
(
sales_date DATE,
sales_id NUMBER,
sales_city VARCHAR(20),
....
)
PARTITION BY HASH(sales_id)
(
PARTITION part_1,
PARTITION part_2,
PARTITION part_3,
PARTITION part_4
) TABLESPACE SYS_TBS_DISK_DATA;
The example above shows that the table_part_table with four partitions is created using hash partitioning. Each partition manages data divided according to the hash function HASH(sales_id, 4). The above example is shown in [Figure 7-25].
Operations on Hash-Partitioned Objects#
There are four types of operations that can be performed on hash-partitioned objects. These are ADD PARTITION, COALESCE PARTITION, RENAME PARTITION, and TRUNCATE PARTITION.
-
To add a partition to a hash-partitioned object, use ADD PARTITION.
-
To get rid of a partition but keep its data, use COALESCE PARTITION. This operation deletes the last partition and reorganizes the entire partitioned object, combining the records from the deleted partition with the existing records in all other partitions.
-
To rename a partition, use RENAME PARTITION.
-
To truncate a partition, that is, delete all of the records in the partition, use TRUNCATE PARTITION.
ADD PARTITION#
Adding a partition to a hash-partitioned object means increasing the number of hash keys. Adding a partition affects all existing partitions. If a hash key is changed, the entire records of the table are reorganized into the new partitions. The following figure illustrates the process of adding a partition.
In the example shown in the above figure, the part_5 partition is added to a partitioned object that originally consists of 4 partitions. The new part_5 partition is created, and ② the records in the four existing partitions are redistributed among the four existing partitions and the newly created partition.
COALESCE PARTITION#
'Coalescing' the partitions of a hash-partitioned object means decreasing the number of hash keys. Coalescing a partition affects all existing partitions. To coalesce the partitions of a hash-partitioned object, use COALESCE PARTITION. The last partition is deleted, and the records from the deleted partition are redistributed throughout the partitioned object together with the existing records. When coalescing partitions, the name of the partition to delete cannot be specified. The partitions are deleted one by one, starting with the last one and ending with the first one.
For example, if a hash-partitioned object comprising 4 partitions (part_1, part_2, part_3 and part_4) is coalesced, it is reduced to a partitioned object comprising 3 partitions (part_1, part_2 and part_3). The following figure illustrates this process of coalescing partitions.
In the example shown in the above figure, the partitions of a hash-partitioned object consisting of 4 partitions are coalesced. ① The records in the four existing partitions are redistributed among the part_1, part_2 and part_3 partitions, and ② the last partition, part_4, is deleted
RENAME PARTITION#
RENAME PARTITION changes the name of a partition without changing the partition conditions.
TRUNCATE PARTITION#
TRUNCATE PARTITION deletes all of the records from a partition without changing the partition conditions.
Range Partitioning using Hash#
Range partitioning using hash is a method of partitioning an object by specifying the range based on the hash value of a partition key. Partition key only allows single column. Range partitioning using hash is typically used for uniform load distribution and manageability.
Unlike hash partitioning, range partitioning using hash supports SPLIT PARTITION, DROP PARTITION, and MERGE PARTITION but not COALESCE PARTITION.
The default execution is same as range partitioning and supports default partition. However, only single columns are allowed for partition key and the range of hash value is 0 to 1000.
The following is an example of range partitioning using hash.
CREATE TABLE part_table
(
sales_date DATE,
sales_id NUMBER,
sales_city VARCHAR(20),
....
)
PARTITION BY RANGE_USING_HASH(sales_id)
(
PARTITION part_1 VALUES LESS THAN ( 250 ),
PARTITION part_2 VALUES LESS THAN ( 500 ),
PARTITION part_3 VALUES LESS THAN ( 750 ),
PARTITION part_def VALUES DEFAULT
) TABLESPACE SYS_TBS_DISK_DATA;
The table creating statement above can be shown as figure below.
Operations on Hash-Partitioned Objects#
The operation on hash using range partitioned object is the same as Operations on Range-Partitioned Objects.