Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Table Partitioning

Oracle Usage

The purpose of database partitioning is to provide support for very large tables and indexes by splitting them into smaller pieces. Each partition has its own name and definitions. They can be managed separately or collectively as one object. From an application perspective, partitions are transparent. Partitioned tables behave the same as non-partitioned tables allowing your applications access using unmodified SQL statements. Table partitioning provides several benefits:

Oracle 18c introduces the following enhancements to partitioning.

Oracle 19 introduces hybrid partitioned tables: partitions can now be both internal Oracle tables and external tables and sources. It is also possible to integrate both internal and external partitions together in a single partitioned table.

Hash Table Partitioning

When a partition key is specified (for example, a table column with a NUMBER data type), Oracle applies a hashing algorithm to evenly distribute the data (records) among all defined partitions. The partitions have approximately the same size.

The following example creates a hash partitioned table.

CREATE TABLE SYSTEM_LOGS
  (EVENT_NO NUMBER NOT NULL,
  EVENT_DATE DATE NOT NULL,
  EVENT_STR VARCHAR2(500),
  ERROR_CODE VARCHAR2(10))
  PARTITION BY HASH (ERROR_CODE)
  PARTITIONS 3
  STORE IN (TB1, TB2, TB3);

List Table Partitioning

You can specify a list of discrete values for the table partitioning key in the description of each partition. This type of table partitioning enables control over partition organization using explicit values. For example, partition events by error code values.

The following example creates a list-partitioned table.

CREATE TABLE SYSTEM_LOGS
  (EVENT_NO NUMBER NOT NULL,
  EVENT_DATE DATE NOT NULL,
  EVENT_STR VARCHAR2(500),
  ERROR_CODE VARCHAR2(10))
  PARTITION BY LIST (ERROR_CODE)
  (PARTITION warning VALUES ('err1', 'err2', 'err3') TABLESPACE TB1,
  PARTITION critical VALUES ('err4', 'err5', 'err6') TABLESPACE TB2);

Range Table Partitioning

Partition a table based on a range of values. The Oracle database assigns rows to table partitions based on column values falling within a given range. Range table partitioning is one of the most frequently used type of partitioning, primarily with date values. Range table partitioning can also be implemented with numeric ranges (1-10000, 10001- 20000…).

The following example creates a range-partitioned table.

CREATE TABLE SYSTEM_LOGS
  (EVENT_NO NUMBER NOT NULL,
  EVENT_DATE DATE NOT NULL,
  EVENT_STR VARCHAR2(500))
  PARTITION BY RANGE (EVENT_DATE)
  (PARTITION EVENT_DATE VALUES
    LESS THAN (TO_DATE('01/01/2015',
    'DD/MM/YYYY')) TABLESPACE TB1,
  PARTITION EVENT_DATE VALUES
    LESS THAN (TO_DATE('01/01/2016',
    'DD/MM/YYYY')) TABLESPACE TB2,
  PARTITION EVENT_DATE VALUES
    LESS THAN (TO_DATE('01/01/2017',
    'DD/MM/YYYY')) TABLESPACE TB3);

Composite Table Partitioning

With composite partitioning, a table can be partitioned by one data distribution method, and then each partition can be further subdivided into sub-partitions using the same, or different, data distribution method(s). For example:

Partitioning Extensions

Oracle provides additional partitioning strategies that enhance the capabilities of basic partitioning. These partitioning strategies include:

Split Partitions

You can use the SPLIT PARTITION statement to redistribute the contents of one partition, or sub-partition, into multiple partitions or sub-partitions.

ALTER TABLE SPLIT PARTITION p0 INTO
  (PARTITION P01 VALUES LESS THAN (100), PARTITION p02);

Exchange Partitions

The EXCHANGE PARTITION statement is useful to exchange table partitions in or out of a partitioned table.

ALTER TABLE orders EXCHANGE
  PARTITION p_ord3 WITH TABLE orders_year_2016;

Subpartitioning Tables

You can create subpartitions within partitions to further split the parent partition.

PARTITION BY RANGE(department_id)
  SUBPARTITION BY HASH(last_name)
  SUBPARTITION TEMPLATE
    (SUBPARTITION a TABLESPACE ts1,
    SUBPARTITION b TABLESPACE ts2,
    SUBPARTITION c TABLESPACE ts3,
    SUBPARTITION d TABLESPACE ts4)
  (PARTITION p1 VALUES LESS THAN (1000),
  PARTITION p2 VALUES LESS THAN (2000),
  PARTITION p3 VALUES LESS THAN (MAXVALUE)

For more information, see Partitioning Concepts in the Oracle documentation.

Automatic List Partitioning

Oracle 12c introduces automatic list partitioning. This enhancement enables automatic creation of new partitions for new values inserted into a list-partitioned table. An automatic list-partitioned table is created with only one partition. The database creates the additional table partitions automatically.

The following example creates an automatic list-partitioned table.

CREATE TABLE SYSTEM_LOGS
  (EVENT_NO NUMBER NOT NULL,
  EVENT_DATE DATE NOT NULL,
  EVENT_STR VARCHAR2(500),
  ERROR_CODE VARCHAR2(10))
  PARTITION BY LIST (ERROR_CODE) AUTOMATIC
  (PARTITION warning VALUES ('err1', 'err2', 'err3'))

For more information, see Oracle Partitioning in the Oracle documentation.

MySQL Usage

The Table Partitioning mechanism in MySQL is similar to Oracle and contains most of the Oracle table partitioning features. The only items not supported in MySQL table partitioning are the automatic features such as interval partitioning and automatic list partitioning. You can implement these features using triggers or procedures. For more information, see Partitioning in the MySQL documentation.

MySQL Basic Table Partitioning Methods

Hash Table Partitioning

Partitioning by hash is used mostly to achieve an even distribution of data between the partitions. Make sure that you specify a column value or expression based on a column value to be hashed and the number of partitions into which the partitioned table is to be divided when creating the partitioned table.

Make sure that you use an SQL expression that returns an integer for the hash expression. The only permitted data types beside integer are date types and one of the following functions:

ABS, CEILING, DAY, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, DATEDIFF, EXTRACT, FLOOR, HOUR, MICROSECOND, MINUTE, MOD, MONTH, QUARTER, SECOND, TIME_TO_SEC, TO_DAYS, TO_SECONDS, UNIX_TIMESTAMP (with TIMESTAMP columns), WEEKDAY, YEAR, YEARWEEK

For other column types you can use KEY partitioning, which takes any column used as part or all of the table’s primary key.

Examples

The following example creates a hash-partitioned table.

CREATE TABLE SYSTEM_LOGS
    (EVENT_NO INT NOT NULL,
    EVENT_DATE DATE NOT NULL,
    EVENT_STR VARCHAR(500),
    ERROR_CODE INT)
    PARTITION BY HASH (ERROR_CODE)
    PARTITIONS 3;

The following example creates a key-partitioned table.

CREATE TABLE SYSTEM_LOGS
    (EVENT_NO INT NOT NULL,
    EVENT_DATE DATE NOT NULL,
    EVENT_STR VARCHAR(500),
    ERROR_CODE VARCHAR(10) PRIMARY KEY)
    PARTITION BY KEY()
    PARTITIONS 3;

For more information, see HASH Partitioning and KEY Partitioning in the MySQL documentation.

List Table Partitioning

As with the hash partition, make sure that this partitioned column in INT. To use LIST on varchar, use LIST COLUMNS.

Examples

The following example creates a list-partitioned table.

CREATE TABLE SYSTEM_LOGS
    (EVENT_NO INT NOT NULL,
    EVENT_DATE DATE NOT NULL,
    EVENT_STR VARCHAR(500),
    ERROR_CODE INT)
    PARTITION BY LIST (ERROR_CODE)
    (PARTITION warning VALUES IN (3345, 5423,3332),
    PARTITION critical VALUES IN (9786, 9231, 6321));

The following example creates a list-columns-partition table.

CREATE TABLE SYSTEM_LOGS
    (EVENT_NO INT NOT NULL,
    EVENT_DATE DATE NOT NULL,
    EVENT_STR VARCHAR(500),
    ERROR_CODE VARCHAR(500))
    PARTITION BY LIST COLUMNS (ERROR_CODE)
    (PARTITION warning VALUES IN ('err1', 'err2', 'err3'),
    PARTITION critical VALUES IN ('err4', 'err5', 'err6'));

For more information, see LIST Partitioning in the MySQL documentation.

Range Table Partitioning

Similar to a list partition, you can use a range partition on integer values or RANGE COLUMNS for DATE or DATETIME.

Examples

The following example creates a range-partitioned table.

CREATE TABLE SYSTEM_LOGS
    (EVENT_NO INT NOT NULL,
    EVENT_DATE DATE NOT NULL,
    EVENT_STR VARCHAR(500))
    PARTITION BY RANGE (YEAR(EVENT_DATE))
    (PARTITION p0 VALUES LESS THAN (2015),
    PARTITION p1 VALUES LESS THAN (2016),
    PARTITION p2 VALUES LESS THAN (2017));

The following example creates a range columns-partitioned table.

CREATE TABLE SYSTEM_LOGS
    (EVENT_NO INT NOT NULL,
    EVENT_DATE DATE NOT NULL,
    EVENT_STR VARCHAR(500))
    PARTITION BY RANGE COLUMNS (EVENT_DATE)
    (PARTITION p0 VALUES LESS THAN ('2015-01-01'),
    PARTITION p1 VALUES LESS THAN ('2016-01-01'),
    PARTITION p2 VALUES LESS THAN ('2017-01-01'));

For more information, see RANGE Partitioning in the MySQL documentation.

Composite Table Partitioning

With composite partitioning, a table can be partitioned by one data distribution method, and then each partition can be further subdivided into sub-partitions using the same, or different, data distribution methods.

In MySQL 5.7, you can subpartition tables that are partitioned by range or list. Subpartitions may use either hash or key partitioning.

You can use the following approaches:

Examples

The following example creates a range-key subpartition. All partitions have two subpartitions.

CREATE TABLE EMPLOYESS
    (DEPARTMENT_ID INT NOT NULL,
    LAST_NAME VARCHAR(50) NOT NULL,
    FIRST_NAME VARCHAR(50),
    PRIMARY KEY (DEPARTMENT_ID, LAST_NAME))
    PARTITION BY RANGE(DEPARTMENT_ID)
    SUBPARTITION BY KEY (last_name)
    SUBPARTITIONS 2
        (PARTITION p1 VALUES LESS THAN (10),
        PARTITION p2 VALUES LESS THAN (20),
        PARTITION p3 VALUES LESS THAN (MAXVALUE));

For more information, see Subpartitioning in the MySQL documentation.

Split Partitions

In Oracle, SPLIT PARTITION STATEMENT translates to REORGANIZE PARTITION in MySQL. Create a list partition and then split one of the partitions.

You can split range partitions at the last partition only.

CREATE TABLE SYSTEM_LOGS
    (EVENT_NO INT NOT NULL,
    EVENT_DATE DATE NOT NULL,
    EVENT_STR VARCHAR(500),
    ERROR_CODE VARCHAR(500))
    PARTITION BY LIST COLUMNS (ERROR_CODE)
    (PARTITION warning VALUES IN ('err1', 'err2', 'err3'),
    PARTITION critical VALUES IN ('err4', 'err5', 'err6'));

ALTER TABLE SYSTEM_LOGS REORGANIZE PARTITION warning INTO
    (PARTITION warning0 VALUES IN ('err2.5', 'err3.5'),
    PARTITION warning1 VALUES IN ('err2.8', 'err3.8'));

For more information, see Management of RANGE and LIST Partitions in the MySQL documentation.

Exchange Partitions

Similar to Oracle, you can exchange tables with partitions.

ALTER TABLE orders
    EXCHANGE PARTITION p_ord3 WITH TABLE orders_year_2016;

For more information, see Exchanging Partitions and Subpartitions with Tables in the MySQL documentation.

Summary