SUMMARY: This article provides information about the types and benefits of table partitioning and using the table partitioning syntax compatible with Oracle databases supported by EDB’s Advanced Server (EDB’s Postgres database).
1. Benefits of Partitioning
2. Types of Partitioning
3. Partitioning Syntax
Partitioning: It is the method by which we can divide a database table into segments (partitions).
Benefits of Partitioning
In a partitioned table, one logically large table is divided into smaller physical pieces. Table partitioning gives you several benefits:
- Query performance can be improved significantly in situations when the most heavily accessed table rows are in a single partition or a small number of partitions. Partitioning gives you the option to omit the partition column from the front of an index, thereby reducing index size and making it more likely that the heavily used parts of the index fit in memory.
- You will get the performance benefits when a query in the execution accesses a large percentage of a single partition as the server will perform a sequential scan of the partition instead of using an index and random access reads, which are scattered across the whole table.
- A bulk load (or unload) can be implemented by adding or removing partitions, if you include that requirement into the partitioning design. ALTER TABLE is a better option than a bulk operation. It also entirely avoids the VACUUMING overhead caused by a bulk DELETE.
- Seldom-used data could even be migrated to a less-expensive (or slower) storage media.
A good rule of thumb for table partitioning is that the size of the table should largely exceed the physical memory of the database server with the benefit of table partitioning largely depending on the application.
Types of Partitioning
List Partitioning
While creating a list-partitioned table, a single partitioning key column is defined. The server then compares the defined set of key values in the partitioning rule with the corresponding column within the row, and if this column value matches any value in the partitioning rule, the row is stored in the partition that is named in the rule.
Range Partitioning
When we create a range-partitioned table, we need to specify one or more partitioning key columns. After we add a new row to the table, the server compares the value of the partitioning key (or keys) to the corresponding column (or columns) in the table entry. If the column values satisfy the conditions per the partitioning rule, the row is stored within the partition named within the rule.
Interval Partitioning
Interval partitioning is simply an extension to range partitioning that allows a database to automatically create a new partition when the newly inserted data goes over the range of an existing partitioning. To implement interval partitioning, we need to include the INTERVAL clause and also need to specify the range size for a new partition.
Hash Partitioning
When you create a hash-partitioned table, one or more partitioning key columns are defined. Data is divided into (approximately) equal-sized partitions among the defined partitions. Adding a row to a hash-partitioned table, the server computes a hash value for the data in the defined column (or columns) and stores the row in a partition in line with the hash value.
Subpartitioning
Subpartitioning breaks a partitioned table into smaller subsets. All subsets need to be stored in the same database server cluster. A table is often subpartitioned by a distinct set of columns and a subpartitioning type that is distinct from the parent partition. If one partition is subpartitioned, then each partition will have a minimum of one subpartition. If a table is subpartitioned, no data is stored in any of the partition tables; the data is stored instead in the corresponding subpartitions.
Consult the Database Compatibility for Oracle Developer’s Guide for more information on table partitioning syntax compatible with Oracle databases supported by EDB’s Advanced Server.
Partitioning Syntax
The following table provides information about using the table partitioning syntaxes compatible with Oracle databases supported by EDB’s Advanced Server.
|
Name |
Syntax |
Example |
|
List Partitioning |
CREATE TABLE [ schema. ]table_name table_definition PARTITION BY LIST(column) [SUBPARTITION BY {RANGE|LIST|HASH} (column[, column ]...)] (list_partition_definition[, list_partition_definition]...);
Where list_partition_definition is:
PARTITION [partition_name] VALUES (value[, value]...) [TABLESPACE tablespace_name] [(subpartition, ...)] |
The following example creates a partitioned table (sales) using the PARTITION BY LIST clause. The sales table stores information in three partitions (europe, asia, and americas):
CREATE TABLE sales ( dept_no number, part_no varchar2, country varchar2(20), date date, amount number
) PARTITION BY LIST(country) ( PARTITION europe VALUES('FRANCE', 'ITALY'), PARTITION asia VALUES('INDIA', 'PAKISTAN'), PARTITION americas VALUES('US', 'CANADA') ); The resulting table is partitioned by the value specified in the country column. |
|
Range Partitioning |
CREATE TABLE [ schema. ]table_name table_definition PARTITION BY RANGE(column[, column ]...) [INTERVAL (constant | expression)] [SUBPARTITION BY {RANGE|LIST|HASH} (column[, column ]...)] (range_partition_definition[, range_partition_definition]...);
Where range_partition_definition is:
PARTITION [partition_name] VALUES LESS THAN (value[, value]...) [TABLESPACE tablespace_name] [(subpartition, ...)] |
The following example creates a partitioned table (sales) using the PARTITION BY RANGE clause. The sales table stores information in four partitions (q1_2012,q2_2012, q3_2012 and q4_2012): CREATE TABLE sales ( dept_no number, part_no varchar2, country varchar2(20), date date, amount number ) PARTITION BY RANGE(date) (
PARTITION q1_2012 VALUES LESS THAN('2012-Apr-01'), PARTITION q2_2012 VALUES LESS THAN('2012-Jul-01'), PARTITION q3_2012 VALUES LESS THAN('2012-Oct-01'), PARTITION q4_2012 VALUES LESS THAN('2013-Jan-01') ); The resulting table is partitioned by the value specified in the date column. |
|
Hash Partitioning |
CREATE TABLE [ schema. ]table_name table_definition PARTITION BY HASH(column[, column ]...) [SUBPARTITION BY {RANGE|LIST|HASH} (column[, column ]...)] (hash_partition_definition[, hash_partition_definition]...);
Where hash_partition_definition is:
[PARTITION partition_name] [TABLESPACE tablespace_name] [(subpartition, ...)] |
The following example creates a partitioned table (sales) using the PARTITION BY HASH clause. The sales table stores information in three partitions (p1, p2, and p3):
CREATE TABLE sales ( dept_no number, part_no varchar2,
country varchar2(20), date date, amount number ) PARTITION BY HASH (part_no) ( PARTITION p1, PARTITION p2, PARTITION p3 );
The table will return an empty string for the hash partition value specified in the part_no column. |
|
ALTER TABLE… |
Use the ALTER TABLE… ADD PARTITION command to add a partition to an existing partitioned table. The syntax is:
ALTER TABLE table_name ADD PARTITION partition_definition;
Where partition_definition is:
{list_partition | range_partition }
and list_partition is:
PARTITION [partition_name] VALUES (value[, value]...) [TABLESPACE tablespace_name] [(subpartition, ...)]
and range_partition is:
PARTITION [partition_name]
VALUES LESS THAN (value[, value]...) [TABLESPACE tablespace_name] [(subpartition, ...)]
Where subpartition is:
{list_subpartition | range_subpartition | hash_subpartition}
and list_subpartition is:
SUBPARTITION [subpartition_name] VALUES (value[, value]...) [TABLESPACE tablespace_name]
and range_subpartition is:
SUBPARTITION [subpartition_name ] VALUES LESS THAN (value[, value]...) [TABLESPACE tablespace_name]
|
The example that follows adds a partition to the list-partitioned sales table. Use the following command to create the sales table:
CREATE TABLE sales ( dept_no number, part_no varchar2, country varchar2(20), date date, amount number
) PARTITION BY LIST(country) ( PARTITION europe VALUES('FRANCE', 'ITALY'), PARTITION asia VALUES('INDIA', 'PAKISTAN'), PARTITION americas VALUES('US', 'CANADA') );
The following command adds a partition named east_asia to the sales table:
ALTER TABLE sales ADD PARTITION east_asia VALUES ('CHINA', 'KOREA'); |
|
ALTER TABLE…ADD SUBPARTITION |
The ALTER TABLE… ADD SUBPARTITION command adds a subpartition to an existing subpartitioned partition. The syntax is: ALTER TABLE table_name MODIFY PARTITION partition_name ADD SUBPARTITION subpartition_definition;
Where subpartition_definition is:
{list_subpartition | range_subpartition}
and list_subpartition is:
SUBPARTITION [subpartition_name] VALUES (value[, value]...) [TABLESPACE tablespace_name]
and range_subpartition is:
SUBPARTITION [subpartition_name] VALUES LESS THAN (value[, value]...) [TABLESPACE tablespace_name]
|
The following example adds a RANGE subpartition to the list-partitioned sales table. Use the following command to create the sales table:
CREATE TABLE sales ( dept_no number, part_no varchar2, country varchar2(20), date date, amount number
) PARTITION BY LIST(country) SUBPARTITION BY RANGE(date) ( PARTITION europe VALUES('FRANCE', 'ITALY') ( SUBPARTITION europe_2011 VALUES LESS THAN('2012-Jan-01'), SUBPARTITION europe_2012 VALUES LESS THAN('2013-Jan-01') ), PARTITION asia VALUES('INDIA', 'PAKISTAN') (
SUBPARTITION asia_2011 VALUES LESS THAN('2012-Jan-01'), SUBPARTITION asia_2012 VALUES LESS THAN('2013-Jan-01') ), PARTITION americas VALUES('US', 'CANADA') ( SUBPARTITION americas_2011 VALUES LESS THAN('2012-Jan-01'), SUBPARTITION americas_2012 VALUES LESS THAN('2013-Jan-01') ) );
The following command adds a subpartition named europe_2013:
ALTER TABLE sales MODIFY PARTITION europe ADD SUBPARTITION europe_2013 VALUES LESS THAN('2015-Jan-01');
After invoking the command, the table includes a subpartition named europe_2013. |
|
ALTER TABLE… |
Use the ALTER TABLE… RENAME PARTITION command to rename a table partition. The syntax takes two forms.
The first form renames a partition:
ALTER TABLE table_name RENAME PARTITION partition_name TO new_name;
The second form renames a subpartition:
ALTER TABLE table_name RENAME SUBPARTITION subpartition_name TO new_name; |
The following command creates a list-partitioned table named sales:
CREATE TABLE sales ( dept_no number, part_no varchar2, country varchar2(20), date date, amount number ) PARTITION BY LIST(country) ( PARTITION europe VALUES('FRANCE', 'ITALY'), PARTITION asia VALUES('INDIA', 'PAKISTAN'), PARTITION americas VALUES('US', 'CANADA') );
The following command renames the americas partition to n_america:
ALTER TABLE sales RENAME PARTITION americas TO n_america; |
|
ALTER TABLE… |
Use the ALTER TABLE… DROP PARTITION command to delete a partition definition, and the data stored in that partition. The syntax is:
ALTER TABLE table_name DROP PARTITION partition_name; |
The example that follows deletes a partition of the sales table. Use the following command to create the sales table:
CREATE TABLE sales ( dept_no number, part_no varchar2, country varchar2(20), date date, amount number ) PARTITION BY LIST(country) ( PARTITION europe VALUES('FRANCE', 'ITALY'), PARTITION asia VALUES('INDIA', 'PAKISTAN'), PARTITION americas VALUES('US', 'CANADA') );
To delete the americas partition from the sales table, invoke the following command:
ALTER TABLE sales DROP PARTITION americas; |
|
ALTER TABLE…DROP SUBPARTITION |
Use the ALTER TABLE… DROP SUBPARTITION command to drop a subpartition definition, and the data stored in that subpartition. The syntax is:
ALTER TABLE table_name DROP SUBPARTITION subpartition_name; |
The example that follows deletes a subpartition of the sales table. Use the following command to create the sales table:
CREATE TABLE sales ( dept_no number, part_no varchar2, country varchar2(20), date date, amount number ) PARTITION BY RANGE(date) SUBPARTITION BY LIST (country) ( PARTITION first_half_2012 VALUES LESS THAN('01-JUL-2012') ( SUBPARTITION europe VALUES ('ITALY', 'FRANCE'), SUBPARTITION americas VALUES ('CANADA', 'US'), SUBPARTITION asia VALUES ('PAKISTAN', 'INDIA') ), PARTITION second_half_2012 VALUES LESS THAN('01-JAN-2013') );
To delete the americas subpartition from the sales table, invoke the following command:
ALTER TABLE sales DROP SUBPARTITION americas; |
|
ALTER TABLE… |
Use the ALTER TABLE… TRUNCATE PARTITION command to remove the data from the specified partition, leaving the partition structure intact. The syntax is:
ALTER TABLE table_name TRUNCATE PARTITION partition_name [{DROP|REUSE} STORAGE] |
The example that follows removes the data from a partition of the sales table. Use the following command to create the sales table:
CREATE TABLE sales ( dept_no number, part_no varchar2, country varchar2(20), date date, amount number ) PARTITION BY LIST(country) ( PARTITION europe VALUES('FRANCE', 'ITALY'), PARTITION asia VALUES('INDIA', 'PAKISTAN'), PARTITION americas VALUES('US', 'CANADA') );
INSERT INTO sales VALUES (10, '4519b', 'FRANCE', '17-Jan-2012', '45000'), (20, '3788a', 'INDIA', '01-Mar-2012', '75000'), (40, '9519b', 'US', '12-Apr-2012', '145000'), (20, '3788a', 'PAKISTAN', '04-Jun-2012', '37500'), (40, '4577b', 'US', '11-Nov-2012', '25000'), (30, '7588b', 'CANADA', '14-Dec-2012', '50000'), (30, '9519b', 'CANADA', '01-Feb-2012', '75000'), (30, '4519b', 'CANADA', '08-Apr-2012', '120000'), (40, '3788a', 'US', '12-May-2012', '4950'), (10, '9519b', 'ITALY', '07-Jul-2012', '15000'), (10, '9519a', 'FRANCE', '18-Aug-2012', '650000'), (10, '9519b', 'FRANCE', '18-Aug-2012', '650000'), (20, '3788b', 'INDIA', '21-Sept-2012', '5090'), (40, '4788a', 'US', '23-Sept-2012', '4950'), (40, '4788b', 'US', '09-Oct-2012', '15000'), (20, '4519a', 'INDIA', '18-Oct-2012', '650000'), (20, '4519b', 'INDIA', '2-Dec-2012', '5090');
To delete the contents of the americas partition, invoke the following command:
ALTER TABLE sales TRUNCATE PARTITION americas; |
|
ALTER TABLE… |
Use the ALTER TABLE… TRUNCATE SUBPARTITION command to remove all of the data from the specified subpartition, leaving the subpartition structure intact. The syntax is:
ALTER TABLE table_name
TRUNCATE SUBPARTITION subpartition_name [{DROP|REUSE} STORAGE]
|
The example that follows removes the data from a subpartition of the sales table. Use the following command to create the sales table:
CREATE TABLE sales ( dept_no number, part_no varchar2, country varchar2(20), date date, amount number ) PARTITION BY RANGE(date) SUBPARTITION BY LIST (country) ( PARTITION "2011" VALUES LESS THAN('01-JAN-2012') ( SUBPARTITION europe_2011 VALUES ('ITALY', 'FRANCE'), SUBPARTITION asia_2011 VALUES ('PAKISTAN', 'INDIA'), SUBPARTITION americas_2011 VALUES ('US', 'CANADA') ),
PARTITION "2012" VALUES LESS THAN('01-JAN-2013') ( SUBPARTITION europe_2012 VALUES ('ITALY', 'FRANCE'), SUBPARTITION asia_2012 VALUES ('PAKISTAN', 'INDIA'), SUBPARTITION americas_2012 VALUES ('US', 'CANADA') ),
PARTITION "2013" VALUES LESS THAN('01-JAN-2015') ( SUBPARTITION europe_2013 VALUES ('ITALY', 'FRANCE'), SUBPARTITION asia_2013 VALUES ('PAKISTAN', 'INDIA'), SUBPARTITION americas_2013 VALUES ('US', 'CANADA') ) );
INSERT INTO sales VALUES (10, '4519b', 'FRANCE', '17-Jan-2011', '45000'), (20, '3788a', 'INDIA', '01-Mar-2012', '75000'), (40, '9519b', 'US', '12-Apr-2012', '145000'), (20, '3788a', 'PAKISTAN', '04-Jun-2012', '37500'), (40, '4577b', 'US', '11-Nov-2012', '25000'), (30, '7588b', 'CANADA', '14-Dec-2011', '50000'), (30, '4519b', 'CANADA', '08-Apr-2012', '120000'), (40, '3788a', 'US', '12-May-2011', '4950'), (20, '3788a', 'US', '04-Apr-2012', '37500'), (40, '4577b', 'INDIA', '11-Jun-2011', '25000'), (10, '9519b', 'ITALY', '07-Jul-2012', '15000'), (20, '4519b', 'INDIA', '2-Dec-2012', '5090');
To delete the contents of the 2012_americas partition, invoke the following command:
ALTER TABLE sales TRUNCATE SUBPARTITION "americas_2012"; |