OceanBase：分区表

原创李真旭 2020-05-29

5664

这是Oceanbase学习系列第2篇文章，重点测试一下ob对于分区表的支持情况（注意我这里测试均为ob的oracle模式)，供大家参考。

首先我们来看下对于分区的支持有哪些：

partition_option:
PARTITION BY HASH(expression)
[subpartition_option] PARTITIONS partition_count
| PARTITION BY KEY([column_name_list])
[subpartition_option] PARTITIONS partition_count
| PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
[subpartition_option] (range_partition_list)
复制

从文档来看，OB目前支持hash,range,key 3种分区方式(其中key就类似list分区);同时也支持复合分区(即二级子分区);我们今天先来测试一下普通分区的情况。

1)key分区(list)测试

obclient> create table enmotech(a number,b number)
-> partition by list(a)
-> (partition part_1 values ((1),(2)),
->  partition part_2 values ((3),(4)),
->  partition part_3 values ((5),(6)),
->  partition part_4 values(default)
-> );
Query OK, 0 rows affected (0.05 sec)
obclient> insert into enmotech values(1,100);
Query OK, 1 row affected (1.14 sec)
obclient> insert into enmotech values(2,101);
Query OK, 1 row affected (0.00 sec)
obclient> insert into enmotech values(3,100);
Query OK, 1 row affected (0.01 sec)
obclient> insert into enmotech values(4,102);
Query OK, 1 row affected (0.00 sec)
obclient> insert into enmotech values(5,112);
Query OK, 1 row affected (0.00 sec)
obclient> insert into enmotech values(8,9999999);
Query OK, 1 row affected (0.00 sec)
obclient> commit;
Query OK, 0 rows affected (0.00 sec)
obclient> select * from enmotech;
+------+---------+
| A    | B       |
+------+---------+
|    1 |     100 |
|    2 |     101 |
|    3 |     100 |
|    4 |     102 |
|    5 |     112 |
|    8 | 9999999 |
+------+---------+
6 rows in set (0.01 sec)
obclient> 
obclient> select table_owner,table_name,PARTITION_NAME,PCT_USED,TABLESPACE_NAME,NUM_ROWS,COMPRESSION,LAST_ANALYZED
-> from dba_tab_partitions where table_name=upper('enmotech');
+-------------+------------+----------------+----------+-----------------+----------+-------------+---------------+
| TABLE_OWNER | TABLE_NAME | PARTITION_NAME | PCT_USED | TABLESPACE_NAME | NUM_ROWS | COMPRESSION | LAST_ANALYZED |
+-------------+------------+----------------+----------+-----------------+----------+-------------+---------------+
| ROGER       | ENMOTECH   | PART_4         |     NULL | NULL            |     NULL | DISABLED    | NULL          |
| ROGER       | ENMOTECH   | PART_3         |     NULL | NULL            |     NULL | DISABLED    | NULL          |
| ROGER       | ENMOTECH   | PART_2         |     NULL | NULL            |     NULL | DISABLED    | NULL          |
| ROGER       | ENMOTECH   | PART_1         |     NULL | NULL            |     NULL | DISABLED    | NULL          |
+-------------+------------+----------------+----------+-----------------+----------+-------------+---------------+
4 rows in set (0.03 sec)
obclient> explain select * from enmotech where a=3 \G;
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR  |NAME    |EST. ROWS|COST|
---------------------------------------
|0 |TABLE SCAN|ENMOTECH|1        |37  |
=======================================
Outputs & filters: 
-------------------------------------
0 - output([ENMOTECH.A], [ENMOTECH.B]), filter([ENMOTECH.A = 3]), 
access([ENMOTECH.A], [ENMOTECH.B]), partitions(p1)
1 row in set (0.00 sec)
ERROR: 
No query specified
obclient> select * from enmotech partition (part_2);
+------+------+
| A    | B    |
+------+------+
|    3 |  100 |
|    4 |  102 |
+------+------+
2 rows in set (0.00 sec)
obclient> 
obclient> explain select * from enmotech where a=7 \G;
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR  |NAME    |EST. ROWS|COST|
---------------------------------------
|0 |TABLE SCAN|ENMOTECH|1        |37  |
=======================================
Outputs & filters: 
-------------------------------------
0 - output([ENMOTECH.A], [ENMOTECH.B]), filter([ENMOTECH.A = 7]), 
access([ENMOTECH.A], [ENMOTECH.B]), partitions(p3)
1 row in set (0.00 sec)
ERROR: 
No query specified
obclient> alter table enmotech drop partition PART_2;
Query OK, 0 rows affected (0.04 sec)
obclient> purge recyclebin;
Query OK, 0 rows affected (0.03 sec)
obclient> show recyclebin;
Empty set (0.00 sec)
obclient> alter table enmotech add partition values ((3),(4)) ;
ERROR-00600: internal error code, arguments: -5598, cannot add partition when DEFAULT partition exists
obclient> 
obclient> alter table enmotech drop partition PART_3;
Query OK, 0 rows affected (0.02 sec)
obclient> show recyclebin;
Empty set (0.01 sec)
obclient>  alter table enmotech truncate partition part_1;
ERROR-00600: internal error code, arguments: -4007, truncate partition not supported
obclient>
复制

我们可以发现ob能够很好支持list分区，同时也支持分区的drop操作；但是不支持truncate分区操作。同时如何存在default 分区的情况下，也无法进行add partition操作.另外对于分区的drop，默认是不会存放到回收站的。也就是说ob的flashback table功能无法支持分区表(至于flashback database行不行，稍后再测试)。

另外可以看到，sql语句条件指定分区键，执行计划会进行分区裁剪，需要注意的是分区编号默认从p0开始。

range分区测试

obclient> create table enmotech_p(a number,b number)
-> partition by range(a)
-> (partition p_1 values less than (10),
-> partition p_2 values less than (20),
-> partition p_3 values less than (30)
-> );
Query OK, 0 rows affected (0.05 sec)
obclient> insert into enmotech_p values(1,10000);
Query OK, 1 row affected (0.03 sec)
obclient> insert into enmotech_p values(11,10000); 
Query OK, 1 row affected (0.01 sec)
obclient> insert into enmotech_p values(22,10000);  
Query OK, 1 row affected (0.01 sec)
obclient> insert into enmotech_p values(33,10000);  
ERROR-14400: inserted partition key does not map to any partition
obclient> alter table enmotech_p add partition p_max values(default);
ERROR-00600: internal error code, arguments: -4016, Ooooooooooooops
obclient> 
obclient> alter table enmotech_p add partition p_max values(maxvalue);
ERROR-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'maxvalue)' at line 1
obclient> 
obclient> alter table enmotech_p add partition p_max values(99999999999999999999);
Query OK, 0 rows affected (0.05 sec)
obclient> insert into enmotech_p values(33,10000);  
Query OK, 1 row affected (0.01 sec)
obclient> insert into enmotech_p values(33333333,10000); 
Query OK, 1 row affected (0.00 sec)
obclient> commit;
Query OK, 0 rows affected (0.00 sec)
obclient> select * from enmotech_p;
+----------+-------+
| A        | B     |
+----------+-------+
|        1 | 10000 |
|       11 | 10000 |
|       22 | 10000 |
|       33 | 10000 |
| 33333333 | 10000 |
+----------+-------+
5 rows in set (0.00 sec)
复制

可以看到,目前ob2.2版本还不支持max分区。那么是否支持global或者local 索引呢？

obclient> create index idx_enmotech_a on enmotech_p(a) local;
Query OK, 0 rows affected (0.43 sec)
obclient> select * from enmotech_p where a=33;
+------+-------+
| A    | B     |
+------+-------+
|   33 | 10000 |
+------+-------+
1 row in set (0.00 sec)
obclient> explain select * from enmotech_p where a=33 \G;
*************************** 1. row ***************************
Query Plan: =========================================
|ID|OPERATOR  |NAME      |EST. ROWS|COST|
-----------------------------------------
|0 |TABLE SCAN|ENMOTECH_P|1        |37  |
=========================================
Outputs & filters: 
-------------------------------------
0 - output([ENMOTECH_P.A], [ENMOTECH_P.B]), filter([ENMOTECH_P.A = 33]), 
access([ENMOTECH_P.A], [ENMOTECH_P.B]), partitions(p3)
1 row in set (0.00 sec)
ERROR: 
No query specified
obclient> 
obclient> select OWNER,INDEX_NAME,TABLE_NAME,PARTITIONING_TYPE,LOCALITY
-> from dba_part_indexes where TABLE_NAME=upper('enmotech_p');
+-------+----------------+------------+-------------------+----------+
| OWNER | INDEX_NAME     | TABLE_NAME | PARTITIONING_TYPE | LOCALITY |
+-------+----------------+------------+-------------------+----------+
| ROGER | IDX_ENMOTECH_A | ENMOTECH_P | RANGE_COL         | LOCAL    |
+-------+----------------+------------+-------------------+----------+
1 row in set (0.10 sec)
obclient> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,INTERVAL,SEGMENT_CREATED,BLEVEL
-> from dba_ind_partitions where INDEX_owner='ROGER';
Empty set (0.02 sec)
复制

这里不知道为什么，居然查不到？看来ob的oracle模式数据字典方面兼容性还不够好。

那么对于global index的支持呢？

obclient>  create index idx_enmotech_a1 on  enmotech_p(a) global;
ERROR-01408: such column list already indexed
obclient> drop index idx_enmotech_a;
Query OK, 0 rows affected (0.02 sec)
obclient> create index idx_enmotech_a1 on  enmotech_p(a) global;
ERROR-00600: internal error code, arguments: -4007, create global index on table without primary key not supported
obclient> 
obclient> alter table enmotech_p add constraint enmotech_a_pk primary key (a);
ERROR-00600: internal error code, arguments: -4007, Not supported feature or function
obclient> alter table enmotech_p truncate partition p_3;
ERROR-00600: internal error code, arguments: -4007, truncate partition not supported
obclient>
复制

发现ob居然不支持add 主键约束，当然是分区情况下。最后查看官方文档发现语法有差异：

alter_table_action:
ADD [COLUMN] {column_definition | (column_definition_list)}
| CHANGE [COLUMN] column_name column_definition
| MODIFY [COLUMN] column_definition
| ALTER [COLUMN] column_name {SET DEFAULT const_value | DROP DEFAULT}
| DROP [COLUMN] column_name
| ADD [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} [index_name] index_desc
| ADD {INDEX | KEY} [index_name] index_desc
| ADD FULLTEXT [INDEX | KEY] [index_name] fulltext_index_desc
| ALTER INDEX index_name [VISIBLE | INVISIBLE]
| DROP {INDEX | KEY} index_name
| ADD PARTITION (range_partition_list)
| DROP PARTITION (partition_name_list)
| [SET] table_option_list
| RENAME [TO] table_name
| DROP TABLEGROUP
复制

可以看到不支持add primary key，但是可以add unique index。另外从命令上来看也不支持move partition操作。不过总的来说功能也还算完善了。而且还在支持invisable index呢？这可是Oracle 11g才有的新功能。

hash分区测试

obclient>  create table enmo_hash(a number,b varchar2(20))
-> partition by hash(a) partitions 8;
Query OK, 0 rows affected (0.57 sec)
obclient> 
obclient> insert into enmo_hash values(1,'xxx');
Query OK, 1 row affected (0.16 sec)
obclient> set global autocommit=on;
Query OK, 0 rows affected (0.01 sec)
obclient> insert into enmo_hash values(2,'xxx'); 
Query OK, 1 row affected (0.03 sec)
obclient> insert into enmo_hash values(10,'xxx'); 
Query OK, 1 row affected (0.01 sec)
obclient> insert into enmo_hash values(110,'xxx'); 
Query OK, 1 row affected (0.00 sec)
obclient> insert into enmo_hash values(1110,'xxx'); 
Query OK, 1 row affected (0.00 sec)
obclient> insert into enmo_hash values(11100,'xxx');
Query OK, 1 row affected (0.00 sec)
obclient> insert into enmo_hash values(12200,'xxx');  
Query OK, 1 row affected (0.27 sec)
obclient> insert into enmo_hash values(1220,'xxx');  
Query OK, 1 row affected (0.03 sec)
obclient> insert into enmo_hash values(3220,'xxx');   
Query OK, 1 row affected (0.00 sec)
obclient> insert into enmo_hash values(3230,'xxx'); 
Query OK, 1 row affected (0.10 sec)
obclient> select * from enmo_hash;
+-------+------+
| A     | B    |
+-------+------+
|     2 | xxx  |
|  1110 | xxx  |
|  3220 | xxx  |
|  3230 | xxx  |
|  1220 | xxx  |
| 11100 | xxx  |
| 12200 | xxx  |
|     1 | xxx  |
|   110 | xxx  |
|    10 | xxx  |
+-------+------+
10 rows in set (0.28 sec)
obclient> explain select * from enmo_hash where a=110 \G;
*************************** 1. row ***************************
Query Plan: ========================================
|ID|OPERATOR  |NAME     |EST. ROWS|COST|
----------------------------------------
|0 |TABLE SCAN|ENMO_HASH|1        |37  |
========================================
Outputs & filters: 
-------------------------------------
0 - output([ENMO_HASH.A], [ENMO_HASH.B]), filter([ENMO_HASH.A = 110]), 
access([ENMO_HASH.A], [ENMO_HASH.B]), partitions(p5)
1 row in set (0.01 sec)
ERROR: 
No query specified
obclient> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,PCT_FREE,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,NUM_ROWS
-> from dba_tab_partitions where table_name=upper('enmo_hash');
+------------+----------------+------------+--------------------+----------+-----------+-----------+----------------+-------------+----------+
| TABLE_NAME | PARTITION_NAME | HIGH_VALUE | PARTITION_POSITION | PCT_FREE | INI_TRANS | MAX_TRANS | INITIAL_EXTENT | NEXT_EXTENT | NUM_ROWS |
+------------+----------------+------------+--------------------+----------+-----------+-----------+----------------+-------------+----------+
| ENMO_HASH  | p7             | NULL       |                  7 |     NULL |      NULL |      NULL |           NULL |        NULL |     NULL |
| ENMO_HASH  | p6             | NULL       |                  6 |     NULL |      NULL |      NULL |           NULL |        NULL |     NULL |
| ENMO_HASH  | p5             | NULL       |                  5 |     NULL |      NULL |      NULL |           NULL |        NULL |     NULL |
| ENMO_HASH  | p4             | NULL       |                  4 |     NULL |      NULL |      NULL |           NULL |        NULL |     NULL |
| ENMO_HASH  | p3             | NULL       |                  3 |     NULL |      NULL |      NULL |           NULL |        NULL |     NULL |
| ENMO_HASH  | p2             | NULL       |                  2 |     NULL |      NULL |      NULL |           NULL |        NULL |     NULL |
| ENMO_HASH  | p1             | NULL       |                  1 |     NULL |      NULL |      NULL |           NULL |        NULL |     NULL |
| ENMO_HASH  | p0             | NULL       |                  0 |     NULL |      NULL |      NULL |           NULL |        NULL |     NULL |
+------------+----------------+------------+--------------------+----------+-----------+-----------+----------------+-------------+----------+
8 rows in set (0.18 sec)
obclient> select count(1) from enmo_hash;
+----------+
| COUNT(1) |
+----------+
|       10 |
+----------+
1 row in set (0.21 sec)
obclient> select count(1) from enmo_hash partition (p0);
+----------+
| COUNT(1) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)
obclient> select count(1) from enmo_hash partition (p1);
+----------+
| COUNT(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)
obclient> select count(1) from enmo_hash partition (p2);
+----------+
| COUNT(1) |
+----------+
|        1 |
+----------+
1 row in set (0.15 sec)
obclient> select count(1) from enmo_hash partition (p3);
+----------+
| COUNT(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
obclient> select count(1) from enmo_hash partition (p4);
+----------+
| COUNT(1) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)
obclient> select count(1) from enmo_hash partition (p5);
+----------+
| COUNT(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
obclient> select count(1) from enmo_hash partition (p6);
+----------+
| COUNT(1) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)
obclient> select count(1) from enmo_hash partition (p7);
+----------+
| COUNT(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
obclient> 
obclient> insert into enmo_hash select * from enmo_hash;
Query OK, 10 rows affected (0.28 sec)
Records: 10  Duplicates: 0  Warnings: 0
obclient> insert into enmo_hash select * from enmo_hash;
Query OK, 20 rows affected (0.20 sec)
Records: 20  Duplicates: 0  Warnings: 0
obclient> insert into enmo_hash select * from enmo_hash;
Query OK, 40 rows affected (0.06 sec)
Records: 40  Duplicates: 0  Warnings: 0
......
obclient> insert into enmo_hash select * from enmo_hash;
Query OK, 327680 rows affected (5.69 sec)
Records: 327680  Duplicates: 0  Warnings: 0
obclient> insert into enmo_hash select * from enmo_hash;
ERROR-00600: internal error code, arguments: -4012, Timeout
obclient> 
obclient> show variables like '%timeout%';
+---------------------+-----------+
| VARIABLE_NAME       | VALUE     |
+---------------------+-----------+
| connect_timeout     | 10        |
| interactive_timeout | 28800     |
| net_read_timeout    | 30        |
| net_write_timeout   | 60        |
| ob_query_timeout    | 10000000  |
| ob_trx_idle_timeout | 120000000 |
| ob_trx_timeout      | 100000000 |
| wait_timeout        | 28800     |
+---------------------+-----------+
8 rows in set (0.09 sec)
obclient> show variables like '%commit%';
+---------------+-------+
| VARIABLE_NAME | VALUE |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.05 sec)
obclient> set ob_query_timeout=1000000000;
Query OK, 0 rows affected (0.04 sec)
obclient> set ob_trx_timeout=1000000000;
Query OK, 0 rows affected (0.04 sec)
obclient> insert into enmo_hash select * from enmo_hash;
Query OK, 655360 rows affected (14.38 sec)
Records: 655360  Duplicates: 0  Warnings: 0
obclient>

复制

从SQL语法上来讲，几乎跟Oracle partition一致，这一点很赞。这下Oracle dba可以无缝切换了。在进行数据insert时，我strace了一下observer进程，我们来看看情况。

[root@td1 yum.repos.d]# ps -ef|grep obs
root       529   450  0 10:55 pts/1    00:00:00 grep --color=auto obs
admin     4003     1 99 May27 ?        19:53:43 /home/admin/oceanbase/bin/observer -i ens192 -P 2882 -p 2881 -z zone1 -d /home/admin/oceanbase/store/obdemo -r 192.168.101.41:2882:2881 -c 20200525 -n obdemo -l ERROR -o memory_limit=36G,system_memory=10G,datafile_size=60G,config_additional_dir=/data/1/obdemo/etc3;/data/log1/obdemo/etc2
[root@td1 yum.repos.d]# strace -fr -o /tmp/ob.log -p 4003
strace: Process 4003 attached with 869 threads
^Cstrace: Process 4003 detached
strace: Process 4004 detached
.......
[root@td1 tmp]# cat ob.log |grep 'fildes='|head -20
4467       0.000006 io_submit(140663265980416, 1, [{data=0x7fec901098e0, pwrite, fildes=1681, str="\0\1\0\0\0\1\0\0\0\0\0\0\20\0\0\0\0\0\0\0\17U\0\5\246\254w\2652\345\0\0"..., nbytes=4096, offset=40599552}] <unfinished ...>
4467       0.000008 io_submit(140663265980416, 1, [{data=0x7fec901098e0, pwrite, fildes=1681, 
......
str="\306\32\4\300\250e)\0\0\vB\0\5\246\234[\345\306\32\0\5\246\254w\327\247}\0\0\0\0`"..., nbytes=4096, offset=40603648}] <unfinished ...>
4467       0.000004 io_submit(140663265980416, 1, [{data=0x7fec901098e0, pwrite, fildes=1681, str="\306\32\4\300\250e)\0\0\vB\0\5\246\234[\345\306\32\0\5\246\254w\327\247}\0\0\0\0`"..., nbytes=4096, offset=40603648}] <unfinished ...>
[root@td1 tmp]# 
[root@td1 tmp]# cat ob.log |grep 'fildes='|head -100|awk '{print $3 $7 $9 $10}'|sort |uniq -c
9 io_submit(140663265980416,fildes=1681,\2\5\5?\377\0\26\2-\377"...,nbytes=4096,
1 io_submit(140663265980416,fildes=1681,\2\5\5?\377\0\26\2-\377"...,nbytes=8192,
9 io_submit(140663265980416,fildes=1681,nbytes=4096,offset=40599552}]
13 io_submit(140663265980416,fildes=1681,nbytes=4096,offset=40603648}]
8 io_submit(140663265980416,fildes=1681,nbytes=4096,offset=40607744}]
10 io_submit(140663265980416,fildes=1681,nbytes=4096,offset=40611840}]
10 io_submit(140663265980416,fildes=1681,nbytes=4096,offset=40615936}]
3 io_submit(140663265980416,fildes=1681,nbytes=4096,offset=40624128}]
12 io_submit(140663265980416,fildes=1681,nbytes=4096,offset=40628224}]
13 io_submit(140663265980416,fildes=1681,nbytes=4096,offset=40632320}]
4 io_submit(140663265980416,fildes=1681,nbytes=4096,offset=40636416}]
1 io_submit(140663265980416,fildes=1681,nbytes=8192,offset=40599552}]
1 io_submit(140663265980416,fildes=1681,nbytes=8192,offset=40603648}]
1 io_submit(140663265980416,fildes=1681,nbytes=8192,offset=40607744}]
1 io_submit(140663265980416,fildes=1681,nbytes=8192,offset=40611840}]
1 io_submit(140663265980416,fildes=1681,nbytes=8192,offset=40615936}]
1 io_submit(140663265980416,fildes=1681,nbytes=8192,offset=40624128}]
1 io_submit(140663265980416,fildes=1681,nbytes=8192,offset=40628224}]
1 io_submit(140663265980416,fildes=1681,nbytes=8192,offset=40632320}]
[root@td1 fd]# ls -ltr|awk '{print $11}'|grep '/data/'| more
/data/1/obdemo/sstable/block_file
/data/log1/obdemo/slog
/data/log1/obdemo/ilog
/data/log1/obdemo/clog
/data/log1/obdemo/slog/1
/data/log1/obdemo/clog/5
[root@td1 fd]# ls -ltr > /tmp/ob_fd.log
[root@td1 fd]# cat /tmp/ob_fd.log |grep '/data'
lrwx------ 1 admin admin 64 May 28 10:58 740 -> /data/1/obdemo/sstable/block_file
lr-x------ 1 admin admin 64 May 28 10:58 737 -> /data/log1/obdemo/slog
lr-x------ 1 admin admin 64 May 28 10:58 1004 -> /data/log1/obdemo/ilog
lr-x------ 1 admin admin 64 May 28 10:58 1001 -> /data/log1/obdemo/clog
lrwx------ 1 admin admin 64 May 28 10:58 1983 -> /data/log1/obdemo/slog/1
lrwx------ 1 admin admin 64 May 28 10:58 1681 -> /data/log1/obdemo/clog/5
复制

不难看出ob这里使用了异步IO;但是每一笔交易都必须刷日志落盘到clog. clog即commit log. OB这里的写模式看起来很特殊，后面再研究一下。最后我们来看看ob 的oracle兼容模式下，hash分区的数据是否均衡.

obclient> select count(1) from enmo_hash;
+----------+
| COUNT(1) |
+----------+
|  1310720 |
+----------+
1 row in set (1.38 sec)
obclient> select count(1) from enmo_hash partition (p0);
+----------+
| COUNT(1) |
+----------+
|   393216 |
+----------+
1 row in set (0.38 sec)
obclient> select count(1) from enmo_hash partition (p1);
+----------+
| COUNT(1) |
+----------+
|   131072 |
+----------+
1 row in set (0.14 sec)
obclient> select count(1) from enmo_hash partition (p2);
+----------+
| COUNT(1) |
+----------+
|   131072 |
+----------+
1 row in set (0.13 sec)
obclient> select count(1) from enmo_hash partition (p3);
+----------+
| COUNT(1) |
+----------+
|   262144 |
+----------+
1 row in set (0.25 sec)
obclient> select count(1) from enmo_hash partition (p4);
+----------+
| COUNT(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
obclient> select count(1) from enmo_hash partition (p5);
+----------+
| COUNT(1) |
+----------+
|   262144 |
+----------+
1 row in set (0.24 sec)
obclient> select count(1) from enmo_hash partition (p6);
+----------+
| COUNT(1) |
+----------+
|   131072 |
+----------+
1 row in set (0.13 sec)
obclient> select count(1) from enmo_hash partition (p7);
+----------+
| COUNT(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
复制

下看起来每个hash分区的数据分布并不够均匀。怀疑是测试方式不太对，我再次进行了测试。

obclient>  create table enmo_hash2(USERID NUMBER(38),CREATED date) partition by hash(USERID) partitions 4;
Query OK, 0 rows affected (0.11 sec)
obclient> select USERID,CREATED from sys.DBA_USERS;
+------------------+---------------------+
| USERID           | CREATED             |
+------------------+---------------------+
| 1100611139403782 | 2020-05-27 15:55:55 |
| 1100611139403783 | 2020-05-27 15:55:55 |
| 1100611139403784 | 2020-05-27 15:55:55 |
| 1100611139404827 | 2020-05-27 16:00:12 |
+------------------+---------------------+
4 rows in set (0.01 sec)
obclient> insert into enmo_hash2 select USERID,CREATED from sys.DBA_USERS;
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0
........
obclient> insert into enmo_hash2 select * from enmo_hash2;
Query OK, 12 rows affected (0.02 sec)
Records: 12  Duplicates: 0  Warnings: 0
.......
obclient> insert into enmo_hash2 select * from enmo_hash2;
Query OK, 393216 rows affected (6.26 sec)
Records: 393216  Duplicates: 0  Warnings: 0
obclient> select count(1) from enmo_hash2;
+----------+
| COUNT(1) |
+----------+
|   786432 |
+----------+
1 row in set (0.80 sec)
obclient> select count(1) from enmo_hash2 partition (p0);
+----------+
| COUNT(1) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)
obclient> select count(1) from enmo_hash2 partition (p1);
+----------+
| COUNT(1) |
+----------+
|   589824 |
+----------+
1 row in set (0.55 sec)
obclient> select count(1) from enmo_hash2 partition (p2);
+----------+
| COUNT(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
obclient> select count(1) from enmo_hash2 partition (p3);
+----------+
| COUNT(1) |
+----------+
|   196608 |
+----------+
1 row in set (0.18 sec)
复制

从第二次测试来看数据分布仍然非常不均衡，不知道为什么？知道的朋友请指正一下，谢谢！

这里针对前面的简单测试总结一下：