前一篇文章测试了Oceanbase的普通分区功能；这里继续测试其对于复合分区的支持情况，如下是相关测试过程：
4.1）range-list 测试
obclient> create table enmo_p_second(USERID NUMBER not null,CREATED date not null)
-> partition by range(CREATED)
-> subpartition by list(USERID)
-> subpartition template(
-> subpartition subp0 values('0'),
-> subpartition subp1 values('1'),
-> subpartition subp2 values('2'),
-> subpartition subp3 values('3')
-> )
-> (partition p1 values less than (to_date('2019-03-01','yyyy-mm-dd')),
-> partition p2 values less than (to_date('2019-07-01','yyyy-mm-dd')),
-> partition p3 values less than (to_date('2019-10-01','yyyy-mm-dd')),
-> partition p4 values less than (to_date('2020-02-01','yyyy-mm-dd'))
-> );
Query OK, 0 rows affected (0.39 sec)
obclient> insert into enmo_p_second values(0,'2019-01-02');
ERROR-01843: not a valid month
obclient>
obclient> insert into enmo_p_second values(0,to_date('2019-02-02','yyyy-mm-dd'));
Query OK, 1 row affected (0.00 sec)
obclient> insert into enmo_p_second values(0,to_date('2019-02-04','yyyy-mm-dd'));
Query OK, 1 row affected (0.01 sec)
obclient> insert into enmo_p_second values(1,to_date('2019-03-04','yyyy-mm-dd'));
Query OK, 1 row affected (0.00 sec)
obclient> insert into enmo_p_second values(1,to_date('2019-04-04','yyyy-mm-dd'));
Query OK, 1 row affected (0.00 sec)
obclient> insert into enmo_p_second values(2,to_date('2019-08-04','yyyy-mm-dd'));
Query OK, 1 row affected (0.16 sec)
obclient> insert into enmo_p_second values(2,to_date('2019-09-04','yyyy-mm-dd'));
Query OK, 1 row affected (0.01 sec)
obclient> insert into enmo_p_second values(3,to_date('2019-11-04','yyyy-mm-dd'));
Query OK, 1 row affected (0.03 sec)
obclient> insert into enmo_p_second values(3,to_date('2019-12-04','yyyy-mm-dd'));
Query OK, 1 row affected (0.00 sec)
obclient> commit;
Query OK, 0 rows affected (0.00 sec)
obclient>
obclient> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE,SUBPARTITION_POSITION,INI_TRANS,BLOCKS
-> from dba_tab_subpartitions where table_name=upper('enmo_p_second');
+---------------+----------------+-------------------+------------+-----------------------+-----------+--------+
| TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | HIGH_VALUE | SUBPARTITION_POSITION | INI_TRANS | BLOCKS |
+---------------+----------------+-------------------+------------+-----------------------+-----------+--------+
| ENMO_P_SECOND | P4 | P4sSUBP3 | NULL | 3 | NULL | NULL |
| ENMO_P_SECOND | P4 | P4sSUBP2 | NULL | 2 | NULL | NULL |
| ENMO_P_SECOND | P4 | P4sSUBP1 | NULL | 1 | NULL | NULL |
| ENMO_P_SECOND | P4 | P4sSUBP0 | NULL | 0 | NULL | NULL |
| ENMO_P_SECOND | P3 | P3sSUBP3 | NULL | 3 | NULL | NULL |
| ENMO_P_SECOND | P3 | P3sSUBP2 | NULL | 2 | NULL | NULL |
| ENMO_P_SECOND | P3 | P3sSUBP1 | NULL | 1 | NULL | NULL |
| ENMO_P_SECOND | P3 | P3sSUBP0 | NULL | 0 | NULL | NULL |
| ENMO_P_SECOND | P2 | P2sSUBP3 | NULL | 3 | NULL | NULL |
| ENMO_P_SECOND | P2 | P2sSUBP2 | NULL | 2 | NULL | NULL |
| ENMO_P_SECOND | P2 | P2sSUBP1 | NULL | 1 | NULL | NULL |
| ENMO_P_SECOND | P2 | P2sSUBP0 | NULL | 0 | NULL | NULL |
| ENMO_P_SECOND | P1 | P1sSUBP3 | NULL | 3 | NULL | NULL |
| ENMO_P_SECOND | P1 | P1sSUBP2 | NULL | 2 | NULL | NULL |
| ENMO_P_SECOND | P1 | P1sSUBP1 | NULL | 1 | NULL | NULL |
| ENMO_P_SECOND | P1 | P1sSUBP0 | NULL | 0 | NULL | NULL |
+---------------+----------------+-------------------+------------+-----------------------+-----------+--------+
16 rows in set (0.09 sec)
obclient>
obclient> explain select * from enmo_p_second where userid=2 \G;
*************************** 1. row ***************************
Query Plan: ===========================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
-----------------------------------------------------------
|0 |EXCHANGE IN DISTR | |3960 |341649|
|1 | EXCHANGE OUT DISTR |:EX10000 |3960 |340853|
|2 | PX PARTITION ITERATOR| |3960 |340853|
|3 | TABLE SCAN |ENMO_P_SECOND|3960 |340853|
===========================================================
Outputs & filters:
-------------------------------------
0 - output([ENMO_P_SECOND.USERID], [ENMO_P_SECOND.CREATED]), filter(nil)
1 - output([ENMO_P_SECOND.CREATED], [ENMO_P_SECOND.USERID]), filter(nil), dop=1
2 - output([ENMO_P_SECOND.CREATED], [ENMO_P_SECOND.USERID]), filter(nil)
3 - output([ENMO_P_SECOND.CREATED], [ENMO_P_SECOND.USERID]), filter([ENMO_P_SECOND.USERID = 2]),
access([ENMO_P_SECOND.CREATED], [ENMO_P_SECOND.USERID]), partitions(p0sp2, p1sp2, p2sp2, p3sp2)
1 row in set (0.07 sec)
ERROR:
No query specified
obclient> create index idx_enmo_p_second on ENMO_P_SECOND(created) local;
Query OK, 0 rows affected (0.90 sec)
obclient> select INDEX_NAME,PARTITION_NAME,SUBPARTITION_COUNT,HIGH_VALUE,STATUS from dba_ind_partitions
-> where index_name=upper('idx_enmo_p_second');
Empty set (0.06 sec)
obclient> select OWNER,index_name,TABLE_NAME,INDEX_TYPE,UNIQUENESS,PARTITIONED
-> from dba_indexes where table_name=upper('enmo_p_second');
+-------+-------------------+---------------+------------+------------+-------------+
| OWNER | INDEX_NAME | TABLE_NAME | INDEX_TYPE | UNIQUENESS | PARTITIONED |
+-------+-------------------+---------------+------------+------------+-------------+
| ROGER | IDX_ENMO_P_SECOND | ENMO_P_SECOND | NORMAL | NONUNIQUE | YES |
+-------+-------------------+---------------+------------+------------+-------------+
1 row in set (0.05 sec)
obclient>
可以看到dba_ind_partitions是查不到的。说明数据字典方面兼容性还不是足够好。
4.2） range-range
obclient> create table enmotech_range_range(created date not null, userid number not null,name varchar2(20))
-> partition by range(created)
-> subpartition by range(userid)
-> subpartition template(
-> subpartition sp0 values less than(10),
-> subpartition sp1 values less than(20),
-> subpartition sp2 values less than(30),
-> subpartition sp3 values less than(40)
-> )
-> (partition p0 values less than (to_date('2019-07-01','yyyy-mm-dd')),
-> partition p1 values less than (to_date('2019-10-01','yyyy-mm-dd')),
-> partition p2 values less than (to_date('2020-01-01','yyyy-mm-dd')),
-> partition p3 values less than (to_date('2020-04-01','yyyy-mm-dd'))
-> );
ERROR-00600: internal error code, arguments: -4077, Not implemented feature
从测试来看不支持range-range分区方式。
4.3) range-hash测试
obclient> create table enmotech_range_hash(created date not null, userid number not null,addtime date not null)
-> partition by range(created)
-> subpartition by hash(userid) partitions 8
-> (partition p0 values less than (to_date('2017','yyyy')),
-> partition p1 values less than (to_date('2018','yyyy')),
-> partition p2 values less than (to_date('2019','yyyy')),
-> partition p3 values less than (to_date('2020','yyyy'))
-> );
ERROR-00600: internal error code, arguments: -5282, Wrong number of partitions defined, mismatch with previous setting
obclient> create table enmotech_range_hash(created date not null, userid number not null,addtime date not null)
-> partition by range(created)
-> subpartition by hash(userid) partitions 4
-> (partition p0 values less than (to_date('2017','yyyy')),
-> partition p1 values less than (to_date('2018','yyyy')),
-> partition p2 values less than (to_date('2019','yyyy')),
-> partition p3 values less than (to_date('2020','yyyy'))
-> );
Query OK, 0 rows affected (0.09 sec)
obclient>
obclient> set autocommit=on;
Query OK, 0 rows affected (0.00 sec)
obclient> insert into enmotech_range_hash values(to_date('2016','yyyy'),1,to_date('2016-02-01','yyyy-mm-dd'));
Query OK, 1 row affected (0.02 sec)
obclient> insert into enmotech_range_hash values(to_date('2017','yyyy'),1,to_date('2017-02-01','yyyy-mm-dd'));
Query OK, 1 row affected (0.00 sec)
obclient> insert into enmotech_range_hash values(to_date('2018','yyyy'),1,to_date('2018-02-01','yyyy-mm-dd'));
Query OK, 1 row affected (0.01 sec)
obclient> insert into enmotech_range_hash values(to_date('2019','yyyy'),1,to_date('2019-02-01','yyyy-mm-dd'));
Query OK, 1 row affected (0.01 sec)
obclient> insert into enmotech_range_hash values(to_date('2020','yyyy'),1,to_date('2020-02-01','yyyy-mm-dd'));
ERROR-14400: inserted partition key does not map to any partition
obclient>
obclient> select table_name,PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE,SUBPARTITION_POSITION,TABLESPACE_NAME,SEGMENT_CREATED
-> from dba_tab_subpartitions where table_name=upper('enmotech_range_hash');
+---------------------+----------------+-------------------+------------+-----------------------+-----------------+-----------------+
| TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | HIGH_VALUE | SUBPARTITION_POSITION | TABLESPACE_NAME | SEGMENT_CREATED |
+---------------------+----------------+-------------------+------------+-----------------------+-----------------+-----------------+
| ENMOTECH_RANGE_HASH | P3 | P3sp0 | NULL | 0 | NULL | NULL |
| ENMOTECH_RANGE_HASH | P2 | P2sp0 | NULL | 0 | NULL | NULL |
| ENMOTECH_RANGE_HASH | P1 | P1sp0 | NULL | 0 | NULL | NULL |
| ENMOTECH_RANGE_HASH | P0 | P0sp0 | NULL | 0 | NULL | NULL |
+---------------------+----------------+-------------------+------------+-----------------------+-----------------+-----------------+
4 rows in set (0.12 sec)
obclient> select * from ENMOTECH_RANGE_HASH;
+---------------------+--------+---------------------+
| CREATED | USERID | ADDTIME |
+---------------------+--------+---------------------+
| 2016-06-01 00:00:00 | 1 | 2016-02-01 00:00:00 |
| 2017-06-01 00:00:00 | 1 | 2017-02-01 00:00:00 |
| 2018-06-01 00:00:00 | 1 | 2018-02-01 00:00:00 |
| 2019-06-01 00:00:00 | 1 | 2019-02-01 00:00:00 |
+---------------------+--------+---------------------+
4 rows in set (0.01 sec)
obclient> explain select * from ENMOTECH_RANGE_HASH where created=to_Date('2018','yyyy') \G;
*************************** 1. row ***************************
Query Plan: ==================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
--------------------------------------------------
|0 |TABLE SCAN|ENMOTECH_RANGE_HASH|1 |37 |
==================================================
Outputs & filters:
-------------------------------------
0 - output([ENMOTECH_RANGE_HASH.CREATED], [ENMOTECH_RANGE_HASH.USERID], [ENMOTECH_RANGE_HASH.ADDTIME]), filter([ENMOTECH_RANGE_HASH.CREATED = ?]),
access([ENMOTECH_RANGE_HASH.CREATED], [ENMOTECH_RANGE_HASH.USERID], [ENMOTECH_RANGE_HASH.ADDTIME]), partitions(p2sp0)
1 row in set (0.00 sec)
这里的subpartition语法其实也是不对的，应该写成subpartitions。 但是ob似乎也并没有报错。
4.4) list-range
obclient> create table enmotech_list_range(created date not null, userid number not null,name varchar2(20))
-> partition by list(userid)
-> subpartition by range(created)
-> subpartition template(
-> subpartition sp0 values less than (to_date('2019-07-01','yyyy-mm-dd')),
-> subpartition sp1 values less than (to_date('2019-10-01','yyyy-mm-dd')),
-> subpartition sp2 values less than (to_date('2019-12-01','yyyy-mm-dd')),
-> subpartition sp3 values less than (to_date('2020-07-01','yyyy-mm-dd'))
-> )
-> (partition p0 values ('10'),
-> partition p1 values ('20'),
-> partition p2 values ('30'),
-> partition p3 values ('40')
-> );
Query OK, 0 rows affected (4.09 sec)
obclient>
obclient> insert into enmotech_list_range values (to_date('2019-04-01','yyyy-mm-dd'),10,'killdb.com');
Query OK, 1 row affected (0.01 sec)
obclient> insert into enmotech_list_range values (to_date('2019-08-01','yyyy-mm-dd'),20,'killdb.com');
Query OK, 1 row affected (0.41 sec)
obclient> insert into enmotech_list_range values (to_date('2019-11-01','yyyy-mm-dd'),30,'killdb.com');
Query OK, 1 row affected (0.98 sec)
obclient> insert into enmotech_list_range values (to_date('2019-12-01','yyyy-mm-dd'),40,'killdb.com');
Query OK, 1 row affected (0.02 sec)
obclient> insert into enmotech_list_range values (to_date('2019-13-01','yyyy-mm-dd'),40,'killdb.com');
ERROR-01861: literal does not match format string
obclient> insert into enmotech_list_range values (to_date('2019-11-31','yyyy-mm-dd'),40,'killdb.com');
ERROR-01861: literal does not match format string
obclient> select table_name,PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE,SUBPARTITION_POSITION,TABLESPACE_NAME,SEGMENT_CREATED
-> from dba_tab_subpartitions where table_name=upper('enmotech_list_range');
+---------------------+----------------+-------------------+------------------------------------------------------------------------------------+-----------------------+-----------------+-----------------+
| TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | HIGH_VALUE | SUBPARTITION_POSITION | TABLESPACE_NAME | SEGMENT_CREATED |
+---------------------+----------------+-------------------+------------------------------------------------------------------------------------+-----------------------+-----------------+-----------------+
| ENMOTECH_LIST_RANGE | P3 | P3sSP3 | TO_DATE('2020-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 3 | NULL | NULL |
| ENMOTECH_LIST_RANGE | P3 | P3sSP2 | TO_DATE('2019-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 2 | NULL | NULL |
| ENMOTECH_LIST_RANGE | P3 | P3sSP1 | TO_DATE('2019-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 1 | NULL | NULL |
| ENMOTECH_LIST_RANGE | P3 | P3sSP0 | TO_DATE('2019-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 0 | NULL | NULL |
| ENMOTECH_LIST_RANGE | P2 | P2sSP3 | TO_DATE('2020-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 3 | NULL | NULL |
| ENMOTECH_LIST_RANGE | P2 | P2sSP2 | TO_DATE('2019-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 2 | NULL | NULL |
| ENMOTECH_LIST_RANGE | P2 | P2sSP1 | TO_DATE('2019-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 1 | NULL | NULL |
| ENMOTECH_LIST_RANGE | P2 | P2sSP0 | TO_DATE('2019-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 0 | NULL | NULL |
| ENMOTECH_LIST_RANGE | P1 | P1sSP3 | TO_DATE('2020-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 3 | NULL | NULL |
| ENMOTECH_LIST_RANGE | P1 | P1sSP2 | TO_DATE('2019-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 2 | NULL | NULL |
| ENMOTECH_LIST_RANGE | P1 | P1sSP1 | TO_DATE('2019-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 1 | NULL | NULL |
| ENMOTECH_LIST_RANGE | P1 | P1sSP0 | TO_DATE('2019-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 0 | NULL | NULL |
| ENMOTECH_LIST_RANGE | P0 | P0sSP3 | TO_DATE('2020-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 3 | NULL | NULL |
| ENMOTECH_LIST_RANGE | P0 | P0sSP2 | TO_DATE('2019-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 2 | NULL | NULL |
| ENMOTECH_LIST_RANGE | P0 | P0sSP1 | TO_DATE('2019-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 1 | NULL | NULL |
| ENMOTECH_LIST_RANGE | P0 | P0sSP0 | TO_DATE('2019-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 0 | NULL | NULL |
+---------------------+----------------+-------------------+------------------------------------------------------------------------------------+-----------------------+-----------------+-----------------+
16 rows in set (0.00 sec)
obclient> explain select * from ENMOTECH_RANGE_HASH where userid=20 and created=to_date('2019-08-01','yyyy-mm-dd') \G;
*************************** 1. row ***************************
Query Plan: ==================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
--------------------------------------------------
|0 |TABLE SCAN|ENMOTECH_RANGE_HASH|1 |37 |
==================================================
Outputs & filters:
-------------------------------------
0 - output([ENMOTECH_RANGE_HASH.CREATED], [ENMOTECH_RANGE_HASH.USERID], [ENMOTECH_RANGE_HASH.ADDTIME]), filter([ENMOTECH_RANGE_HASH.USERID = 20], [ENMOTECH_RANGE_HASH.CREATED = ?]),
access([ENMOTECH_RANGE_HASH.CREATED], [ENMOTECH_RANGE_HASH.USERID], [ENMOTECH_RANGE_HASH.ADDTIME]), partitions(p3sp0)
1 row in set (0.00 sec)
ERROR:
No query specified
obclient>
obclient> explain select * from ENMOTECH_RANGE_HASH where created=to_date('2019-08-01','yyyy-mm-dd') \G;
*************************** 1. row ***************************
Query Plan: ==================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
--------------------------------------------------
|0 |TABLE SCAN|ENMOTECH_RANGE_HASH|1 |37 |
==================================================
Outputs & filters:
-------------------------------------
0 - output([ENMOTECH_RANGE_HASH.CREATED], [ENMOTECH_RANGE_HASH.USERID], [ENMOTECH_RANGE_HASH.ADDTIME]), filter([ENMOTECH_RANGE_HASH.CREATED = ?]),
access([ENMOTECH_RANGE_HASH.CREATED], [ENMOTECH_RANGE_HASH.USERID], [ENMOTECH_RANGE_HASH.ADDTIME]), partitions(p3sp0)
1 row in set (0.00 sec)
ERROR:
No query specified
对于list-range支持没有任何问题。
4.5) list-list
obclient> create table enmotech_list_list(created date not null, userid number not null,name varchar2(20))
-> partition by list(userid)
-> subpartition by list(name)
-> subpartition template(
-> subpartition sp0 values ('aa'),
-> subpartition sp1 values ('bb'),
-> subpartition sp2 values ('cc'),
-> subpartition sp3 values ('dd')
-> )
-> (partition p0 values ('10'),
-> partition p1 values ('20'),
-> partition p2 values ('30'),
-> partition p3 values ('40')
-> );
ERROR-00600: internal error code, arguments: -4077, Not implemented feature
可以看到,ob目前暂时不支持list-list分区模式。
4.6) list-hash
obclient> create table enmotech_list_hash(userid number not null, phone number not null,name varchar2(20))
-> partition by list(userid)
-> subpartition by hash(phone) partitions 4
-> (partition p0 values ('10'),
-> partition p1 values ('20'),
-> partition p2 values ('30'),
-> partition p3 values ('40')
-> );
ERROR-00600: internal error code, arguments: -4165, Empty result
obclient> create table enmotech_list_hash(userid number not null, phone number not null,name varchar2(20))
-> partition by list(userid)
-> subpartition by hash(phone) partitions 8
-> (partition p0 values ('10'),
-> partition p1 values ('20'),
-> partition p2 values ('30'),
-> partition p3 values ('40')
-> );
ERROR-02013: Lost connection to MySQL server during query
obclient>
发现这种测试情况下，observer居然crash掉了。如下是observer.log的信息:
[2020-06-05 17:53:58.139804] ERROR [CLOG] is_reconfirm_role_change_or_sync_timeout_ (ob_log_state_mgr.cpp:1274) [8572][1610][Y0-0000000000000000] [lt=21] [dc=0] is_reconfirm_role_change_or_sync_timeout_(partition_key={tid:1099511627777, partition_id:0, part_cnt:1}, now=1591350838139803, last_check_start_id_time_=1591350818133264, max_log_id=70449, start_id=70449, is_wait_replay=false) BACKTRACE:0x726996a 0x72156c5 0x3c8fd19 ...... [2020-06-05 17:54:03.105092] WARN log_user_error_and_warn (ob_rpc_proxy.cpp:288) [7894][264][Y0-0000000000000000] [lt=8] [dc=0] [2020-06-05 17:54:03.236330] ERROR [COMMON] write (ob_log_file_store.cpp:376) [8223][0][Y0-0000000000000000] [lt=0] [dc=0] process get events fail(ret=-4012, new_req_cnt=1, submitted=1, retry_cnt=0, write_fd={flag:2, disk_mgr:0x10c5cac0, file_id:83, is_inited:true, fd_cnt:1}) BACKTRACE:0x726996a 0x72156c5 0x1eeb94e 0x1edd4f8 0x59d1b8f 0x6e67dbd 0x6e687a8 0x647fc59 0x7212e75 0x7212ed8 0x7f06375f4e25 0x7f0636e0a34d [2020-06-05 17:54:03.236434] ERROR [COMMON] process_failed_write (ob_log_file_store.cpp:966) [8223][0][Y0-0000000000000000] [lt=98] [dc=0] write on all disk failed(ret=-4009, fd_cnt=1) BACKTRACE:0x726996a 0x72156c5 0x41612f 0x1edafb5 0x59d171f 0x59d1b02 0x6e67dbd 0x6e687a8 0x647fc59 0x7212e75 0x7212ed8 0x7f06375f4e25 0x7f0636e0a34d [2020-06-05 17:54:03.236473] ERROR [CLOG] flush_buf (ob_clog_file_writer.cpp:787) [8223][0][Y0-0000000000000000] [lt=21] [dc=0] write fail(ret=-4012, buf_write_pos_=12288, file_write_pos=57737216, errno=0) BACKTRACE:0x726996a 0x72156c5 0x39e8b6f 0x39e60ee 0x6e67ef4 0x6e687a8 0x647fc59 0x7212e75 0x7212ed8 0x7f06375f4e25 0x7f0636e0a34d [2020-06-05 17:54:03.236525] ERROR [CLOG] process_log_items (ob_clog_writer.cpp:242) [8223][0][Y0-0000000000000000] [lt=40] [dc=0] log writer write data error, on_fatal_error(ret=-4012, is_disk_error_=true) BACKTRACE:0x726996a 0x72156c5 0x6f26cd 0x2c5a834 0x647ff47 0x7212e75 0x7212ed8 0x7f06375f4e25 0x7f0636e0a34d
重启observer后，再次测试发现语法没有问题. 不过不知道之前observer为什么会crash掉.
obclient> create table enmotech_list_hash(userid number not null, phone number not null,name varchar2(20))
-> partition by list(name)
-> subpartition by hash(userid) partitions 4
-> (partition p0 values ('aa'),
-> partition p1 values ('bb'),
-> partition p2 values ('cc'),
-> partition p3 values ('dd'));
Query OK, 0 rows affected (0.10 sec)
obclient> insert into enmotech_list_hash values(1,123123123,'aa');
Query OK, 1 row affected (0.03 sec)
obclient> insert into enmotech_list_hash values(2,1324223123123,'aa');
Query OK, 1 row affected (0.01 sec)
obclient> insert into enmotech_list_hash values(2,1324223123123,'bb');
Query OK, 1 row affected (0.00 sec)
obclient> insert into enmotech_list_hash values(2,1324223123123,'cc');
Query OK, 1 row affected (0.01 sec)
obclient> insert into enmotech_list_hash values(2,1324223123123,'dd');
Query OK, 1 row affected (0.00 sec)
obclient> commit;
Query OK, 0 rows affected (0.00 sec)
obclient> select table_name,PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE,SUBPARTITION_POSITION,TABLESPACE_NAME,SEGMENT_CREATED from dba_tab_subpartitions where table_name=upper('enmotech_list_hash');
+--------------------+----------------+-------------------+------------+-----------------------+-----------------+-----------------+
| TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | HIGH_VALUE | SUBPARTITION_POSITION | TABLESPACE_NAME | SEGMENT_CREATED |
+--------------------+----------------+-------------------+------------+-----------------------+-----------------+-----------------+
| ENMOTECH_LIST_HASH | P3 | P3sp0 | NULL | 0 | NULL | NULL |
| ENMOTECH_LIST_HASH | P2 | P2sp0 | NULL | 0 | NULL | NULL |
| ENMOTECH_LIST_HASH | P1 | P1sp0 | NULL | 0 | NULL | NULL |
| ENMOTECH_LIST_HASH | P0 | P0sp0 | NULL | 0 | NULL | NULL |
+--------------------+----------------+-------------------+------------+-----------------------+-----------------+-----------------+
4 rows in set (0.14 sec)
上述语法应该是不对的，不过ob这里似乎并没有报错。正确的语法应该是这样：
obclient> create table enmotech_list_hash2(userid number not null, phone number not null,name varchar2(20))
-> partition by list(name)
-> subpartition by hash(userid) subpartitions 4
-> (partition p0 values ('aa'),
-> partition p1 values ('bb'),
-> partition p2 values ('cc'),
-> partition p3 values ('dd'));
Query OK, 0 rows affected (0.11 sec)
obclient> select table_name,PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE,SUBPARTITION_POSITION,TABLESPACE_NAME,SEGMENT_CREATED from dba_tab_subpartitions
-> where table_name=upper('enmotech_list_hash2');
+---------------------+----------------+-------------------+------------+-----------------------+-----------------+-----------------+
| TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | HIGH_VALUE | SUBPARTITION_POSITION | TABLESPACE_NAME | SEGMENT_CREATED |
+---------------------+----------------+-------------------+------------+-----------------------+-----------------+-----------------+
| ENMOTECH_LIST_HASH2 | P3 | P3sp3 | NULL | 3 | NULL | NULL |
| ENMOTECH_LIST_HASH2 | P3 | P3sp2 | NULL | 2 | NULL | NULL |
| ENMOTECH_LIST_HASH2 | P3 | P3sp1 | NULL | 1 | NULL | NULL |
| ENMOTECH_LIST_HASH2 | P3 | P3sp0 | NULL | 0 | NULL | NULL |
| ENMOTECH_LIST_HASH2 | P2 | P2sp3 | NULL | 3 | NULL | NULL |
| ENMOTECH_LIST_HASH2 | P2 | P2sp2 | NULL | 2 | NULL | NULL |
| ENMOTECH_LIST_HASH2 | P2 | P2sp1 | NULL | 1 | NULL | NULL |
| ENMOTECH_LIST_HASH2 | P2 | P2sp0 | NULL | 0 | NULL | NULL |
| ENMOTECH_LIST_HASH2 | P1 | P1sp3 | NULL | 3 | NULL | NULL |
| ENMOTECH_LIST_HASH2 | P1 | P1sp2 | NULL | 2 | NULL | NULL |
| ENMOTECH_LIST_HASH2 | P1 | P1sp1 | NULL | 1 | NULL | NULL |
| ENMOTECH_LIST_HASH2 | P1 | P1sp0 | NULL | 0 | NULL | NULL |
| ENMOTECH_LIST_HASH2 | P0 | P0sp3 | NULL | 3 | NULL | NULL |
| ENMOTECH_LIST_HASH2 | P0 | P0sp2 | NULL | 2 | NULL | NULL |
| ENMOTECH_LIST_HASH2 | P0 | P0sp1 | NULL | 1 | NULL | NULL |
| ENMOTECH_LIST_HASH2 | P0 | P0sp0 | NULL | 0 | NULL | NULL |
+---------------------+----------------+-------------------+------------+-----------------------+-----------------+-----------------+
16 rows in set (0.01 sec)
下面继续测试hash-list和hash-hash：
4.7) hash-list
obclient> create table enmotech_hash_list(created date not null, userid number not null,name varchar2(20))
-> partition by hash(userid) partitions 4
-> subpartition by list(name)
-> subpartition template(
-> subpartition sp0 values ('aa'),
-> subpartition sp1 values ('dd'),
-> subpartition sp2 values ('cc'),
-> subpartition sp3 values ('dd'));
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 'subpartition by list(name)
subpartition template(
subpartition sp0 values ('aa'' at line 3
不支持。
4.8) hash-hash
obclient>
obclient> create table enmotech_hash_hash(userid number not null, phone number not null,name varchar2(20))
-> partition by hash(userid) partitions 4
-> subpartition by hash(phone) subpartitions 4;
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 'subpartition by hash(phone) subpartitions 4' at line 3
obclient>
最后来看看hash-range是否支持：
4.9) hash-range
obclient> create table enmotech_hash_range(created date not null, userid number not null,name varchar2(20))
-> partition by hash(userid)
-> subpartition by range(created)
-> subpartition template(
-> subpartition sp0 values less than (to_date('2019-03-01','yyyy-mm-dd')),
-> subpartition sp1 values less than (to_date('2019-07-01','yyyy-mm-dd')),
-> subpartition sp2 values less than (to_date('2019-10-01','yyyy-mm-dd')),
-> subpartition sp3 values less than (to_date('2020-03-01','yyyy-mm-dd'))
-> )
-> partitions 4;
Query OK, 0 rows affected (0.11 sec)
obclient> insert into enmotech_hash_range values(to_date('2019-01-01','yyyy-mm-dd'),10,'aa');
Query OK, 1 row affected (0.00 sec)
obclient> insert into enmotech_hash_range values(to_date('2019-03-01','yyyy-mm-dd'),10,'aa');
Query OK, 1 row affected (0.01 sec)
obclient> insert into enmotech_hash_range values(to_date('2019-03-01','yyyy-mm-dd'),20,'aa');
Query OK, 1 row affected (0.00 sec)
obclient> insert into enmotech_hash_range values(to_date('2019-08-01','yyyy-mm-dd'),90,'aa');
Query OK, 1 row affected (0.01 sec)
obclient> commit;
Query OK, 0 rows affected (0.00 sec)
obclient> explain select * from enmotech_hash_range where userid=90 \G;
*************************** 1. row ***************************
Query Plan: =================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
-----------------------------------------------------------------
|0 |EXCHANGE IN DISTR | |3960 |350941|
|1 | EXCHANGE OUT DISTR |:EX10000 |3960 |349676|
|2 | PX PARTITION ITERATOR| |3960 |349676|
|3 | TABLE SCAN |ENMOTECH_HASH_RANGE|3960 |349676|
=================================================================
Outputs & filters:
-------------------------------------
0 - output([ENMOTECH_HASH_RANGE.CREATED], [ENMOTECH_HASH_RANGE.USERID], [ENMOTECH_HASH_RANGE.NAME]), filter(nil)
1 - output([ENMOTECH_HASH_RANGE.USERID], [ENMOTECH_HASH_RANGE.CREATED], [ENMOTECH_HASH_RANGE.NAME]), filter(nil), dop=1
2 - output([ENMOTECH_HASH_RANGE.USERID], [ENMOTECH_HASH_RANGE.CREATED], [ENMOTECH_HASH_RANGE.NAME]), filter(nil)
3 - output([ENMOTECH_HASH_RANGE.USERID], [ENMOTECH_HASH_RANGE.CREATED], [ENMOTECH_HASH_RANGE.NAME]), filter([ENMOTECH_HASH_RANGE.USERID = 90]),
access([ENMOTECH_HASH_RANGE.USERID], [ENMOTECH_HASH_RANGE.CREATED], [ENMOTECH_HASH_RANGE.NAME]), partitions(p1sp[0-3])
1 row in set (0.01 sec)
ERROR:
No query specified
obclient> select table_name,PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE,SUBPARTITION_POSITION,TABLESPACE_NAME,SEGMENT_CREATED from dba_tab_subpartitions
-> where table_name=upper('enmotech_hash_range');
+---------------------+----------------+-------------------+------------------------------------------------------------------------------------+-----------------------+-----------------+-----------------+
| TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | HIGH_VALUE | SUBPARTITION_POSITION | TABLESPACE_NAME | SEGMENT_CREATED |
+---------------------+----------------+-------------------+------------------------------------------------------------------------------------+-----------------------+-----------------+-----------------+
| ENMOTECH_HASH_RANGE | p3 | p3sSP3 | TO_DATE('2020-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 3 | NULL | NULL |
| ENMOTECH_HASH_RANGE | p3 | p3sSP2 | TO_DATE('2019-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 2 | NULL | NULL |
| ENMOTECH_HASH_RANGE | p3 | p3sSP1 | TO_DATE('2019-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 1 | NULL | NULL |
| ENMOTECH_HASH_RANGE | p3 | p3sSP0 | TO_DATE('2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 0 | NULL | NULL |
| ENMOTECH_HASH_RANGE | p2 | p2sSP3 | TO_DATE('2020-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 3 | NULL | NULL |
| ENMOTECH_HASH_RANGE | p2 | p2sSP2 | TO_DATE('2019-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 2 | NULL | NULL |
| ENMOTECH_HASH_RANGE | p2 | p2sSP1 | TO_DATE('2019-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 1 | NULL | NULL |
| ENMOTECH_HASH_RANGE | p2 | p2sSP0 | TO_DATE('2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 0 | NULL | NULL |
| ENMOTECH_HASH_RANGE | p1 | p1sSP3 | TO_DATE('2020-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 3 | NULL | NULL |
| ENMOTECH_HASH_RANGE | p1 | p1sSP2 | TO_DATE('2019-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 2 | NULL | NULL |
| ENMOTECH_HASH_RANGE | p1 | p1sSP1 | TO_DATE('2019-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 1 | NULL | NULL |
| ENMOTECH_HASH_RANGE | p1 | p1sSP0 | TO_DATE('2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 0 | NULL | NULL |
| ENMOTECH_HASH_RANGE | p0 | p0sSP3 | TO_DATE('2020-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 3 | NULL | NULL |
| ENMOTECH_HASH_RANGE | p0 | p0sSP2 | TO_DATE('2019-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 2 | NULL | NULL |
| ENMOTECH_HASH_RANGE | p0 | p0sSP1 | TO_DATE('2019-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 1 | NULL | NULL |
| ENMOTECH_HASH_RANGE | p0 | p0sSP0 | TO_DATE('2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | 0 | NULL | NULL |
+---------------------+----------------+-------------------+------------------------------------------------------------------------------------+-----------------------+-----------------+-----------------+
16 rows in set (0.02 sec)
总的来说还是不错，不过这里要注意的是ob对于二级分区支持跟oracle有些不同，必须通过template的分区方式来实现。
最后我们来总结一下oceanbase 2.2 版本Oracle租户模式下,对于二级分区的支持情况：
- rang-range 不支持
- rang-list 支持
- rang-hash 支持
- list-list 不支持
- list-range 支持
- list-hash 支持
- hash-hash 不支持
- hash-range 支持
- hash-list 不支持
另外测试可以发现,ob对于subparition的创建，居然指定paritions关键字也不报错，这应该是一个bug。如有测试不当的地方，欢迎指正。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
