OceanBase系列之–分区表
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: OceanBase系列之–分区表
这是Oceanbase学习系列第2篇文章；重点测试一下ob对于分区表的支持情况（注意我这里测试均为ob的oracle模式);供大家参考。
首先我们来看下对于分区的支持有哪些：
|
1 2 3 4 5 6 7 |
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)测试
|
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 116 117 118 119 120 121 122 123 124 |
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开始。
2) 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 |
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 索引呢？
|
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 |
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的支持呢？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 主键约束，当然是分区情况下。最后查看官方文档发现语法有差异：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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才有的新功能。
3) 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 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 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 |
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进程，我们来看看情况。
|
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 |
[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分区的数据是否均衡.
|
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 |
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分区的数据分布并不够均匀。怀疑是测试方式不太对，我再次进行了测试。
|
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 |
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) |
从第二次测试来看数据分布仍然非常不均衡，不知道为什么？知道的朋友请指正一下，谢谢！
这里针对前面的简单测试总结一下：
1) ob能够较好的支持list,key,hash分区模式；
2) 对于分区的drop操作，回收站是不起作用的；另外不支持truncate partition和move partition操作；
3) 部分兼容Oracle的dba视图看上去数据不太对，说明兼容性还需要提高；
4) SQL条件带分区键，SQL执行时会直接进行分区裁剪，这是标准的分区支持功能;
5) hash分区的数据分布不太均衡，不知道是我的测试方式不对还是是单副本的缘故？
总的来说对oracle的兼容说还不错，还支持了不少的dba_xxx视图，而且ob也有00600错误，???? ～～～～Oracle dba们可以无缝切换啦！
Leave a Reply
You must be logged in to post a comment.