Oceanbase系列之–二级分区测试
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: Oceanbase系列之–二级分区测试
前一篇文章测试了Oceanbase的普通分区功能；这里继续测试其对于复合分区的支持情况，如下是相关测试过程：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
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是查不到的。说明数据字典方面兼容性还不是足够好。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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分区方式。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
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似乎也并没有报错。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 |
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支持没有任何问题。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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分区模式。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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的信息:
|
1 2 3 4 5 6 7 |
[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掉.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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这里似乎并没有报错。正确的语法应该是这样：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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是否支持：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
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。如有测试不当的地方，欢迎指正。
Leave a Reply
You must be logged in to post a comment.