LIST-* 组合分区_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

创建LIST-HASH二级分区

模板化用法

CREATE TABLE tb_l_h_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY HASH(bid) 
SUBPARTITIONS 4
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_l_h_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY HASH(bid) 
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')) SUBPARTITIONS 3,
  PARTITION p2 VALUES IN (DEFAULT) SUBPARTITIONS 2
)

创建LIST-KEY二级分区

模板化用法

CREATE TABLE tb_l_k_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY KEY(bid, name) 
SUBPARTITIONS 4
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_l_k_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY HASH(bid,name) 
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01')) SUBPARTITIONS 3,
  PARTITION p2 VALUES IN (DEFAULT) SUBPARTITIONS 2
)

创建LIST-RANGE二级分区

模板化用法

CREATE TABLE tb_l_r_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY RANGE(bid) 
(
  SUBPARTITION sp1 VALUES LESS THAN(100),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
)
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_l_r_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY RANGE(bid) 

(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')) (
  	SUBPARTITION sp1 VALUES LESS THAN(100),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
	),
  PARTITION p2 VALUES IN (DEFAULT) (
  	SUBPARTITION sp3 VALUES LESS THAN(100),
    SUBPARTITION sp4 VALUES LESS THAN(200),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE)
	)
)

创建LIST-RANGE COLUMNS二级分区

模板化用法

CREATE TABLE tb_l_rc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY RANGE COLUMNS(bid, id) 
(
  SUBPARTITION sp1 VALUES LESS THAN(100,1000),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_l_rc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY RANGE COLUMNS(bid, id) 
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')) (
  	SUBPARTITION sp1 VALUES LESS THAN(100,1000),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
	),
  PARTITION p2 VALUES IN (DEFAULT) (
  	SUBPARTITION sp3 VALUES LESS THAN(100,1000),
    SUBPARTITION sp4 VALUES LESS THAN(200,2000),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE,MAXVALUE)
	)
)

创建LIST-LIST二级分区

模板化用法

CREATE TABLE tb_l_l_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY LIST(id) 
(
  SUBPARTITION sp1 VALUES IN (1000,2000,3000),
  SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_l_l_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY LIST(id) 
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')) (
  	SUBPARTITION sp1 VALUES IN (1000,2000,3000),
  	SUBPARTITION sp2 VALUES IN (DEFAULT)
	),
  PARTITION p2 VALUES IN (DEFAULT) (
  	SUBPARTITION sp3 VALUES IN (1000,2000,3000),
    SUBPARTITION sp4 VALUES IN (4000,5000),
  	SUBPARTITION sp5 VALUES IN (DEFAULT)
	)
)

创建LIST-LIST COLUMNS二级分区

模板化用法

CREATE TABLE tb_l_lc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY LIST COLUMNS(bid,id) 
(
  SUBPARTITION sp1 VALUES IN ((100,1000),(200,2000),(300,3000)),
  SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_l_lc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY LIST COLUMNS(bid,id) 
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')) (
  	SUBPARTITION sp1 VALUES IN ((100,1000),(200,2000),(300,3000)),
  	SUBPARTITION sp2 VALUES IN (DEFAULT)
	),
  PARTITION p2 VALUES IN (DEFAULT) (
  	SUBPARTITION sp3 VALUES IN ((100,1000),(200,2000),(300,3000)),
    SUBPARTITION sp4 VALUES IN ((400,4000),(500,5000)),
  	SUBPARTITION sp5 VALUES IN (DEFAULT)
	)
)