7.OceanBase分区表管理

最新推荐文章于 2024-08-26 16:21:48 发布

戒掉贪嗔痴(薛双奇)

最新推荐文章于 2024-08-26 16:21:48 发布

阅读量2.6k

点赞数

分类专栏：国产数据库-OceanBase 文章标签： oceanbase 数据库

本文链接：https://blog.csdn.net/weixin_43346403/article/details/129384135

版权

国产数据库-OceanBase 专栏收录该内容

39 篇文章 7 订阅

订阅专栏

本文展示了在OceanBase数据库中创建和管理各种类型的分区表的方法，包括RANGE、RANGECOLUMNS、LIST、LISTCOLUMNS、HASH、KEY以及二级分区等。同时，提到了查询分区表、添加分区的操作，并强调了OB分区的一些限制和最佳实践。

摘要由CSDN通过智能技术生成

1.创建分区表

1.创建范围分区。
CREATE TABLE tbl1_r (log_id BIGINT NOT NULL,log_value VARCHAR(50),log_date TIMESTAMP NOT NULL)
       PARTITION BY RANGE(UNIX_TIMESTAMP(log_date)) 
        (PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
       , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
       , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
       , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
       , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
       , PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
       , PARTITION M202007 VALUES LESS THAN(UNIX_TIMESTAMP('2020/08/01'))
       , PARTITION M202008 VALUES LESS THAN(UNIX_TIMESTAMP('2020/09/01'))
       , PARTITION M202009 VALUES LESS THAN(UNIX_TIMESTAMP('2020/10/01'))
       , PARTITION M202010 VALUES LESS THAN(UNIX_TIMESTAMP('2020/11/01'))
       , PARTITION M202011 VALUES LESS THAN(UNIX_TIMESTAMP('2020/12/01'))
       , PARTITION M202012 VALUES LESS THAN(UNIX_TIMESTAMP('2021/01/01'))
        );

		
		
		
2.创建： RANGE COLUMNS 分区。
 CREATE TABLE tbl1_log_rc (log_id BIGINT NOT NULL,log_value VARCHAR(50),log_date DATE NOT NULL)
       PARTITION BY RANGE COLUMNS(log_date) 
        (PARTITION M202001 VALUES LESS THAN('2020/02/01')
       , PARTITION M202002 VALUES LESS THAN('2020/03/01')
       , PARTITION M202003 VALUES LESS THAN('2020/04/01')
       , PARTITION M202004 VALUES LESS THAN('2020/05/01')
       , PARTITION M202005 VALUES LESS THAN('2020/06/01')
       , PARTITION M202006 VALUES LESS THAN('2020/07/01')
       , PARTITION M202007 VALUES LESS THAN('2020/08/01')
       , PARTITION M202008 VALUES LESS THAN('2020/09/01')
       , PARTITION M202009 VALUES LESS THAN('2020/10/01')
       , PARTITION M202010 VALUES LESS THAN('2020/11/01')
       , PARTITION M202011 VALUES LESS THAN('2020/12/01')
       , PARTITION M202012 VALUES LESS THAN('2021/01/01')
       , PARTITION MMAX VALUES LESS THAN MAXVALUE
        );
 
3.创建LIST分区。
CREATE TABLE tbl1_l (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50))
       PARTITION BY LIST(col1) 
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 6),
         PARTITION p2 VALUES IN (DEFAULT)
        ); 
4.创建LIST COLUMNS 分区。
 CREATE TABLE tbl1_lc (id INT,partition_id VARCHAR(2))
       PARTITION BY LIST COLUMNS(partition_id)
        (PARTITION p0 VALUES IN ('00','01'),
         PARTITION p1 VALUES IN ('02','03'),
         PARTITION p2 VALUES IN (DEFAULT)
        );
 
5.创建HASH分区。
CREATE TABLE tbl1_h(col1 INT,col2 VARCHAR(50))
       PARTITION BY HASH(col1) PARTITIONS 60; 
 
6.创建KEY 分区。 
CREATE TABLE tbl1_k(id INT,gmt_create DATETIME,info VARCHAR(20))
       PARTITION BY KEY(id,gmt_create) PARTITIONS 10; 
 
 
7.创建范围二级分区。
 CREATE TABLE tbl_rr(col1 INT,col2 INT,col3 INT)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION mp1 VALUES LESS THAN(100),
         SUBPARTITION mp2 VALUES LESS THAN(200),
         SUBPARTITION mp3 VALUES LESS THAN(300)
        )
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200),
         PARTITION p2 VALUES LESS THAN(300)
        );
 
8.RANGE COLUMNS+RANGE 二级分区。
CREATE TABLE t2_m_rcr (col1 INT NOT NULL,col2 varchar(50),col3 INT NOT NULL) 
PARTITION BY RANGE COLUMNS(col1)
SUBPARTITION BY RANGE(col3)
SUBPARTITION TEMPLATE 
(SUBPARTITION mp0 VALUES LESS THAN(1000),
 SUBPARTITION mp1 VALUES LESS THAN(2000),
 SUBPARTITION mp2 VALUES LESS THAN(3000)
)
(PARTITION p0 VALUES LESS THAN(100),
 PARTITION p1 VALUES LESS THAN(200),
 PARTITION p2 VALUES LESS THAN(300)
);  
 
8.RANGE COLUMNS+RANGE COLUMNS二级分区。
 CREATE TABLE t_m_rcrc(col1 INT,col2 INT) 
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY RANGE COLUMNS(col2)
       SUBPARTITION TEMPLATE 
        (SUBPARTITION mp0 VALUES LESS THAN(1000),
         SUBPARTITION mp1 VALUES LESS THAN(2000),
         SUBPARTITION mp2 VALUES LESS THAN(3000)
        )
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200),
         PARTITION p2 VALUES LESS THAN(300)
        ); 
	

9.RANGE COLUMNS+	LIST COLUMNS 二级分区。
 CREATE TABLE t_m_rclc(col1 INT,col2 INT) 
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
       SUBPARTITION TEMPLATE 
        (SUBPARTITION mp0 VALUES IN(1,3),
         SUBPARTITION mp1 VALUES IN(4,6),
         SUBPARTITION mp2 VALUES IN(7)
        )
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200),
         PARTITION p2 VALUES LESS THAN(300)
        );

2.分区表查看及分区名字查看。

MySQL [oceanbase]> 
SELECT t.table_name,p.part_name,p.part_idx FROM __all_part p 
     LEFT JOIN __all_table t ON p.`table_id`=t.`table_id` 
     ORDER BY t.table_name,p.part_idx;
+-------------+-----------+----------+
| table_name  | part_name | part_idx |
+-------------+-----------+----------+
| t2_m_rcr    | p0        |        0 |
| t2_m_rcr    | p1        |        1 |
| t2_m_rcr    | p2        |        2 |
| tbl1_h      | p0        |        0 |
| tbl1_h      | p1        |        1 |
| tbl1_h      | p2        |        2 |
| tbl1_h      | p3        |        3 |
| tbl1_h      | p4        |        4 |
| tbl1_h      | p5        |        5 |
| tbl1_h      | p6        |        6 |
| tbl1_h      | p7        |        7 |
| tbl1_h      | p8        |        8 |
| tbl1_h      | p9        |        9 |
| tbl1_h      | p10       |       10 |
| tbl1_h      | p11       |       11 |
| tbl1_h      | p12       |       12 |
| tbl1_h      | p13       |       13 |
| tbl1_h      | p14       |       14 |
| tbl1_h      | p15       |       15 |
| tbl1_h      | p16       |       16 |
| tbl1_h      | p17       |       17 |
| tbl1_h      | p18       |       18 |
| tbl1_h      | p19       |       19 |
| tbl1_h      | p20       |       20 |
| tbl1_h      | p21       |       21 |
| tbl1_h      | p22       |       22 |
| tbl1_h      | p23       |       23 |
| tbl1_h      | p24       |       24 |
| tbl1_h      | p25       |       25 |
| tbl1_h      | p26       |       26 |
| tbl1_h      | p27       |       27 |
| tbl1_h      | p28       |       28 |
| tbl1_h      | p29       |       29 |
| tbl1_h      | p30       |       30 |
| tbl1_h      | p31       |       31 |
| tbl1_h      | p32       |       32 |
| tbl1_h      | p33       |       33 |
| tbl1_h      | p34       |       34 |
| tbl1_h      | p35       |       35 |
| tbl1_h      | p36       |       36 |
| tbl1_h      | p37       |       37 |
| tbl1_h      | p38       |       38 |
| tbl1_h      | p39       |       39 |
| tbl1_h      | p40       |       40 |
| tbl1_h      | p41       |       41 |
| tbl1_h      | p42       |       42 |
| tbl1_h      | p43       |       43 |
| tbl1_h      | p44       |       44 |
| tbl1_h      | p45       |       45 |
| tbl1_h      | p46       |       46 |
| tbl1_h      | p47       |       47 |
| tbl1_h      | p48       |       48 |
| tbl1_h      | p49       |       49 |
| tbl1_h      | p50       |       50 |
| tbl1_h      | p51       |       51 |
| tbl1_h      | p52       |       52 |
| tbl1_h      | p53       |       53 |
| tbl1_h      | p54       |       54 |
| tbl1_h      | p55       |       55 |
| tbl1_h      | p56       |       56 |
| tbl1_h      | p57       |       57 |
| tbl1_h      | p58       |       58 |
| tbl1_h      | p59       |       59 |
| tbl1_k      | p0        |        0 |
| tbl1_k      | p1        |        1 |
| tbl1_k      | p2        |        2 |
| tbl1_k      | p3        |        3 |
| tbl1_k      | p4        |        4 |
| tbl1_k      | p5        |        5 |
| tbl1_k      | p6        |        6 |
| tbl1_k      | p7        |        7 |
| tbl1_k      | p8        |        8 |
| tbl1_k      | p9        |        9 |
| tbl1_l      | p0        |        0 |
| tbl1_l      | p1        |        1 |
| tbl1_l      | p2        |        2 |
| tbl1_lc     | p0        |        0 |
| tbl1_lc     | p1        |        1 |
| tbl1_lc     | p2        |        2 |
| tbl1_log_rc | M202001   |        0 |
| tbl1_log_rc | M202002   |        1 |
| tbl1_log_rc | M202003   |        2 |
| tbl1_log_rc | M202004   |        3 |
| tbl1_log_rc | M202005   |        4 |
| tbl1_log_rc | M202006   |        5 |
| tbl1_log_rc | M202007   |        6 |
| tbl1_log_rc | M202008   |        7 |
| tbl1_log_rc | M202009   |        8 |
| tbl1_log_rc | M202010   |        9 |
| tbl1_log_rc | M202011   |       10 |
| tbl1_log_rc | M202012   |       11 |
| tbl1_log_rc | MMAX      |       12 |
| tbl1_r      | M202001   |        0 |
| tbl1_r      | M202002   |        1 |
| tbl1_r      | M202003   |        2 |
| tbl1_r      | M202004   |        3 |
| tbl1_r      | M202005   |        4 |
| tbl1_r      | M202006   |        5 |
| tbl1_r      | M202007   |        6 |
| tbl1_r      | M202008   |        7 |
| tbl1_r      | M202009   |        8 |
| tbl1_r      | M202010   |        9 |
| tbl1_r      | M202011   |       10 |
| tbl1_r      | M202012   |       11 |
| tbl_rr      | p0        |        0 |
| tbl_rr      | p1        |        1 |
| tbl_rr      | p2        |        2 |
| t_m_rclc    | p0        |        0 |
| t_m_rclc    | p1        |        1 |
| t_m_rclc    | p2        |        2 |
| t_m_rcrc    | p0        |        0 |
| t_m_rcrc    | p1        |        1 |
| t_m_rcrc    | p2        |        2 |
+-------------+-----------+----------+
113 rows in set (0.00 sec)

3.分区表查询

MySQL [oceanbase]> select * from sjzt.t2_m_rcr partition(p1);
02001);	
select * from sjzt.tbl1_r partition(M202001);	
select * from sjzt.t_m_rclc partition(p1);	Empty set (0.01 sec)

MySQL [oceanbase]> select * from sjzt.tbl1_h partition(p1);
Empty set (0.10 sec)

MySQL [oceanbase]> select * from sjzt.tbl1_k partition(p1);
Empty set (0.06 sec)

MySQL [oceanbase]> select * from sjzt.tbl1_l partition(p1);
Empty set (0.14 sec)

MySQL [oceanbase]> select * from sjzt.tbl1_lc partition(p1);
Empty set (0.08 sec)

MySQL [oceanbase]> select * from sjzt.tbl1_log_rc partition(M202001);
Empty set (0.06 sec)

MySQL [oceanbase]> select * from sjzt.tbl1_r partition(M202001);
Empty set (0.06 sec)

MySQL [oceanbase]> select * from sjzt.t_m_rclc partition(p1);
Empty set (0.10 sec)

4.添加分区

alter table tbl1_r add partition(partition M202101 VALUES LESS THAN(UNIX_TIMESTAMP('2021/02/01')));	
 --OB分区拆分:不支持。
ALTER TABLE tbl1_log_rc ADD PARTITION  (PARTITION M202101 VALUES LESS THAN ('2021/02/01'));
alter table tbl_rr add partition (PARTITION p3 VALUES LESS THAN(400));

5.总结

OB分区不支持裁剪。
1.分区键必须是主键的子集。
2.分区范围最好不要指定 max 分区，因为早前版本不支持分区split;

3.二级分区增加分区时仅指定一级分区即可，二级分区自动添加。

6.分区表类型。

RANGE  分区
RANGE COLUMNS 分区
LIST 分区
LIST COLUMNS 分区
HASH分区 
KEY 分区 
RANGE+RANGE:范围二级分区 
RANGE COLUMNS+RANGE 二级分区
RANGE COLUMNS+RANGE COLUMNS二级分区。
RANGE COLUMNS+LIST COLUMNS 二级分区。