Composite partition is the partition of each partition in the partition table. The sub partition can use either HASH partition or KEY partition. This is also known as a subarea.

Tip: mysql can only use HASH/KEY partition for sub partition, which is also different from ORACLE.

The following issues need to be noted for composite partitions:

• How to create a composite partition in one partition and have composite partitions in other partitions.
• How to create a composite partition? The number of composite partitions in each partition must be the same.
• The name of the composite partition in the same partition is different. The name of the composite partition in different partitions can be the same.

--The command to create the RANGE-HASH composite partition is as follows:

CREATE TABLE  RHTABLE
(
     EMPNO VARCHAR(20)  NOT NULL ,
     EMPNAME VARCHAR(20) ,
     DEPTNO INT ,
     BIRTHDATE DATE NOT NULL ,
     SALARY INT 
)
PARTITION BY RANGE(SALARY)
SUBPARTITION BY HASH(YEAR(BIRTHDATE))
SUBPARTITIONS 3
(
  PARTITION P1 VALUES LESS THAN(200) ,
  PARTITION P2 VALUES LESS THAN MAXVALUE 
) ;

--The command to create the RANGE-KEY composite partition is as follows:

CREATE TABLE  RKTABLE
(
     EMPNO VARCHAR(20)  NOT NULL ,
     EMPNAME VARCHAR(20) ,
     DEPTNO INT ,
     BIRTHDATE DATE NOT NULL ,
     SALARY INT 
)
PARTITION BY RANGE(SALARY)
SUBPARTITION BY KEY(YEAR(BIRTHDATE))
SUBPARTITIONS 3
(
  PARTITION P1 VALUES LESS THAN(200) ,
  PARTITION P2 VALUES LESS THAN MAXVALUE 
) ;

--The command to create the LIST-HASH composite partition is as follows:

CREATE TABLE  RHTABLE
(
     EMPNO VARCHAR(20)  NOT NULL ,
     EMPNAME VARCHAR(20) ,
     DEPTNO INT ,
     BIRTHDATE DATE NOT NULL ,
     SALARY INT 
)
PARTITION BY LIST(SALARY)
SUBPARTITION BY HASH(YEAR(BIRTHDATE))
SUBPARTITIONS 3
(
  PARTITION P1 VALUES LESS THAN(200) ,
  PARTITION P2 VALUES LESS THAN MAXVALUE 
) ;

--The command to create a LIST-KEY composite partition is as follows:
 

CREATE TABLE  RKTABLE
(
     EMPNO VARCHAR(20)  NOT NULL ,
     EMPNAME VARCHAR(20) ,
     DEPTNO INT ,
     BIRTHDATE DATE NOT NULL ,
     SALARY INT 
)
PARTITION BY LIST(SALARY)
SUBPARTITION BY KEY(YEAR(BIRTHDATE))
SUBPARTITIONS 3
(
  PARTITION P1 VALUES LESS THAN(200) ,
  PARTITION P2 VALUES LESS THAN MAXVALUE 
) ;