Oceanbase系列之–索引&分区索引测试
本站文章除注明转载外,均为本站原创: 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: Oceanbase系列之–索引&分区索引测试
前面2篇文章重点测试了oceanbase 2.2版本对于分区的支持情况。这里重点测试一下索引和分区索引相关的内容。
首先来看下官方文档(2.1版本) 对于index 相关语法的介绍:
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 |
create_index_stmt: CREATE [UNIQUE] INDEX index_name ON table_name (column_desc_list) [index_type] [index_option_list]; drop_index_stmt: DROP INDEX index_name; column_desc_list: column_desc [, column_desc ...] column_desc: column_name [(length)] [ASC | DESC] index_type: USING BTREE index_option_list: index_option [index_option ...] index_option: global_partitioned_index | local_partitioned_index | block_size | compression | STORING(column_name_list) | comment column_name_list: column_name [, column_name ...] global_partitioned_index: [GLOBAL] [partition_option] local_partitioned_index: LOCAL |
不得不说官方文档写的太过于简洁。。。。下面开始测试。
++测试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 43 44 |
obclient> show create table enmotech_part\G; *************************** 1. row *************************** TABLE: ENMOTECH_PART CREATE TABLE: CREATE TABLE "ENMOTECH_PART" ( "A" NUMBER, "B" NUMBER ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10 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), partition P_4 values less than (100), partition P_5 values less than (99999)) 1 row in set (0.05 sec) ERROR: No query specified obclient> obclient> create unique index idx_a_enmoetch_part on enmotech_part(a); ERROR-00600: internal error code, arguments: -4007, create global index on table without primary key not supported obclient> obclient> create unique index idx_a_enmoetch_part on enmotech_part(a) global; ERROR-00600: internal error code, arguments: -4007, create global index on table without primary key not supported obclient> obclient> create unique index idx_a_enmoetch_part on enmotech_part(a) local; ERROR-00600: internal error code, arguments: -5703, Add index failed obclient> create index idx_a_enmoetch_part on enmotech_part(a) local; Query OK, 0 rows affected (0.43 sec) obclient> obclient> select OWNER,TABLE_NAME,INDEX_NAME,index_type,num_rows,LEAF_BLOCKS,DISTINCT_KEYS,PARTITIONED -> from dba_indexes where table_name='ENMOTECH_PART'; +-------+---------------+---------------------+------------+----------+-------------+---------------+-------------+ | OWNER | TABLE_NAME | INDEX_NAME | INDEX_TYPE | NUM_ROWS | LEAF_BLOCKS | DISTINCT_KEYS | PARTITIONED | +-------+---------------+---------------------+------------+----------+-------------+---------------+-------------+ | ROGER | ENMOTECH_PART | IDX_A_ENMOETCH_PART | NORMAL | NULL | NULL | NULL | YES | +-------+---------------+---------------------+------------+----------+-------------+---------------+-------------+ 1 row in set (0.01 sec) obclient> |
我这里表实际上是有重复数据;看上去没有明显的提示。下面重建一个表定义好主键列再试试:
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 |
obclient> create table enmotech_part2(a number not null ,b number not null,c varchar2(20), primary key (a,b)) -> 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), -> partition p_4 values less than (100), -> partition p_5 values less than (99999) -> ); Query OK, 0 rows affected (0.06 sec) obclient> create unique index idx_a_enmoetch_part2 on enmotech_part2(a); Query OK, 0 rows affected (13.47 sec) obclient> create unique index idx_b_enmoetch_part2 on enmotech_part2(b) global; Query OK, 0 rows affected (10.26 sec) obclient> obclient> select OWNER,TABLE_NAME,INDEX_NAME,index_type,num_rows,LEAF_BLOCKS,DISTINCT_KEYS,PARTITIONED -> from dba_indexes where index_name=upper('idx_a_enmoetch_part2'); +-------+----------------+----------------------+------------+----------+-------------+---------------+-------------+ | OWNER | TABLE_NAME | INDEX_NAME | INDEX_TYPE | NUM_ROWS | LEAF_BLOCKS | DISTINCT_KEYS | PARTITIONED | +-------+----------------+----------------------+------------+----------+-------------+---------------+-------------+ | ROGER | ENMOTECH_PART2 | IDX_A_ENMOETCH_PART2 | NORMAL | NULL | NULL | NULL | NO | +-------+----------------+----------------------+------------+----------+-------------+---------------+-------------+ 1 row in set (0.06 sec) obclient> select OWNER,TABLE_NAME,INDEX_NAME,index_type,num_rows,LEAF_BLOCKS,DISTINCT_KEYS,PARTITIONED from dba_indexes where index_name=upper('idx_b_enmoetch_part2'); +-------+----------------+----------------------+------------+----------+-------------+---------------+-------------+ | OWNER | TABLE_NAME | INDEX_NAME | INDEX_TYPE | NUM_ROWS | LEAF_BLOCKS | DISTINCT_KEYS | PARTITIONED | +-------+----------------+----------------------+------------+----------+-------------+---------------+-------------+ | ROGER | ENMOTECH_PART2 | IDX_B_ENMOETCH_PART2 | NORMAL | NULL | NULL | NULL | NO | +-------+----------------+----------------------+------------+----------+-------------+---------------+-------------+ 1 row in set (0.02 sec) obclient> drop index IDX_A_ENMOETCH_PART2; Query OK, 0 rows affected (0.01 sec) obclient> create unique index idx_a1_enmoetch_part2 on enmotech_part2(a) local; Query OK, 0 rows affected (2.33 sec) obclient> select OWNER,TABLE_NAME,INDEX_NAME,index_type,num_rows,LEAF_BLOCKS,DISTINCT_KEYS,PARTITIONED -> from dba_indexes where table_name='ENMOTECH_PART2'; +-------+----------------+--------------------------------------+------------+----------+-------------+---------------+-------------+ | OWNER | TABLE_NAME | INDEX_NAME | INDEX_TYPE | NUM_ROWS | LEAF_BLOCKS | DISTINCT_KEYS | PARTITIONED | +-------+----------------+--------------------------------------+------------+----------+-------------+---------------+-------------+ | ROGER | ENMOTECH_PART2 | ENMOTECH_PART2_OBPK_1591422864362891 | NORMAL | NULL | NULL | NULL | NO | | ROGER | ENMOTECH_PART2 | IDX_B_ENMOETCH_PART2 | NORMAL | NULL | NULL | NULL | NO | | ROGER | ENMOTECH_PART2 | IDX_A1_ENMOETCH_PART2 | NORMAL | NULL | NULL | NULL | YES | +-------+----------------+--------------------------------------+------------+----------+-------------+---------------+-------------+ 3 rows in set (0.01 sec) obclient> drop index IDX_A1_ENMOETCH_PART2; Query OK, 0 rows affected (0.01 sec) obclient> create unique index idx_a1_enmoetch_part2 on enmotech_part2(a) global; Query OK, 0 rows affected (8.26 sec) obclient> select OWNER,TABLE_NAME,INDEX_NAME,index_type,num_rows,LEAF_BLOCKS,DISTINCT_KEYS,PARTITIONED -> from dba_indexes where table_name='ENMOTECH_PART2'; +-------+----------------+--------------------------------------+------------+----------+-------------+---------------+-------------+ | OWNER | TABLE_NAME | INDEX_NAME | INDEX_TYPE | NUM_ROWS | LEAF_BLOCKS | DISTINCT_KEYS | PARTITIONED | +-------+----------------+--------------------------------------+------------+----------+-------------+---------------+-------------+ | ROGER | ENMOTECH_PART2 | ENMOTECH_PART2_OBPK_1591422864362891 | NORMAL | NULL | NULL | NULL | NO | | ROGER | ENMOTECH_PART2 | IDX_B_ENMOETCH_PART2 | NORMAL | NULL | NULL | NULL | NO | | ROGER | ENMOTECH_PART2 | IDX_A1_ENMOETCH_PART2 | NORMAL | NULL | NULL | NULL | NO | +-------+----------------+--------------------------------------+------------+----------+-------------+---------------+-------------+ 3 rows in set (0.01 sec) |
从上述测试来看,定义了主键列的话,可以成功创建local 分区索引。对于全局索引的话,默认都是全局非分区索引。那么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 |
obclient> create index idx_enmotech_part_full on enmotech_part2 (b) -> global 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), -> partition p_4 values less than (100), -> partition p_5 values less than (99999)); ERROR-00600: internal error code, arguments: -5261, A GLOBAL INDEX must include all columns in the table's partitioning function obclient> obclient> create index idx_enmotech_part_full on enmotech_part2 (b,a) -> global 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), -> partition p_4 values less than (100), -> partition p_5 values less than (99999)); ERROR-00600: internal error code, arguments: -4007, partition columns not prefix of index columns not supported obclient> obclient> create index idx_enmotech_part_full on enmotech_part2 (a,b) -> global 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), -> partition p_4 values less than (100), -> partition p_5 values less than (99999)); ERROR-01408: such column list already indexed obclient> drop index IDX_A1_ENMOETCH_PART2; Query OK, 0 rows affected (0.01 sec) obclient> create index idx_enmotech_part_full on enmotech_part2 (a,c) -> global 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), -> partition p_4 values less than (100), -> partition p_5 values less than (99999)); Query OK, 0 rows affected (10.57 sec) obclient> obclient> select OWNER,TABLE_NAME,INDEX_NAME,index_type,num_rows,LEAF_BLOCKS,DISTINCT_KEYS,PARTITIONED -> from dba_indexes where table_name='ENMOTECH_PART2'; +-------+----------------+--------------------------------------+------------+----------+-------------+---------------+-------------+ | OWNER | TABLE_NAME | INDEX_NAME | INDEX_TYPE | NUM_ROWS | LEAF_BLOCKS | DISTINCT_KEYS | PARTITIONED | +-------+----------------+--------------------------------------+------------+----------+-------------+---------------+-------------+ | ROGER | ENMOTECH_PART2 | ENMOTECH_PART2_OBPK_1591422864362891 | NORMAL | NULL | NULL | NULL | NO | | ROGER | ENMOTECH_PART2 | IDX_ENMOTECH_PART_FULL | NORMAL | NULL | NULL | NULL | YES | +-------+----------------+--------------------------------------+------------+----------+-------------+---------------+-------------+ 2 rows in set (0.01 sec) obclient> |
从上面测试来看,对于全局分区索引,oceanbase几乎跟Oracle一致,首先你也指定分区键,同时引用的分区键必须是前导列。
1 2 3 4 5 6 7 8 9 10 11 12 |
obclient> select INDEX_NAME,PARTITION_NAME,STATUS,BLEVEL,NUM_ROWS from dba_ind_partitions -> where index_name='IDX_ENMOTECH_PART_FULL'; +------------------------+----------------+--------+--------+----------+ | INDEX_NAME | PARTITION_NAME | STATUS | BLEVEL | NUM_ROWS | +------------------------+----------------+--------+--------+----------+ | IDX_ENMOTECH_PART_FULL | P_5 | NULL | NULL | NULL | | IDX_ENMOTECH_PART_FULL | P_4 | NULL | NULL | NULL | | IDX_ENMOTECH_PART_FULL | P_3 | NULL | NULL | NULL | | IDX_ENMOTECH_PART_FULL | P_2 | NULL | NULL | NULL | | IDX_ENMOTECH_PART_FULL | P_1 | NULL | NULL | NULL | +------------------------+----------------+--------+--------+----------+ 5 rows in set (0.02 sec) |
我们都知道对于local index索引,Oracle 在表进行DDL时会进行自动维护,而global index 是需要收工维护的或者使用using update global indexes关键字。
那么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 |
obclient> create unique index idx_a1_enmoetch_part2 on enmotech_part2(a) local; Query OK, 0 rows affected (2.22 sec) obclient> select OWNER,TABLE_NAME,INDEX_NAME,PARTITIONED,INDEX_TYPE,STATUS from dba_indexes where table_name=upper('enmotech_part2'); +-------+----------------+--------------------------------------+-------------+------------+--------+ | OWNER | TABLE_NAME | INDEX_NAME | PARTITIONED | INDEX_TYPE | STATUS | +-------+----------------+--------------------------------------+-------------+------------+--------+ | ROGER | ENMOTECH_PART2 | ENMOTECH_PART2_OBPK_1591422864362891 | NO | NORMAL | VALID | | ROGER | ENMOTECH_PART2 | IDX_ENMOTECH_PART_FULL | YES | NORMAL | VALID | | ROGER | ENMOTECH_PART2 | IDX_A1_ENMOETCH_PART2 | YES | NORMAL | VALID | +-------+----------------+--------------------------------------+-------------+------------+--------+ 3 rows in set (0.02 sec) 尝试 truncate 或drop parttion: obclient> alter table enmotech_part2 truncate partition p_3; ERROR-00600: internal error code, arguments: -4007, truncate partition not supported obclient> obclient> alter table enmotech_part2 drop partition p_3; ERROR-00600: internal error code, arguments: -4007, drop or truncate tables with global index not supported obclient> obclient> drop index IDX_ENMOTECH_PART_FULL; Query OK, 0 rows affected (0.03 sec) obclient> alter table enmotech_part2 drop partition p_3; Query OK, 0 rows affected (0.03 sec) obclient> select OWNER,TABLE_NAME,INDEX_NAME,PARTITIONED,INDEX_TYPE,STATUS from dba_indexes where table_name=upper('enmotech_part2'); +-------+----------------+--------------------------------------+-------------+------------+--------+ | OWNER | TABLE_NAME | INDEX_NAME | PARTITIONED | INDEX_TYPE | STATUS | +-------+----------------+--------------------------------------+-------------+------------+--------+ | ROGER | ENMOTECH_PART2 | ENMOTECH_PART2_OBPK_1591422864362891 | NO | NORMAL | VALID | | ROGER | ENMOTECH_PART2 | IDX_A1_ENMOETCH_PART2 | YES | NORMAL | VALID | +-------+----------------+--------------------------------------+-------------+------------+--------+ 2 rows in set (0.01 sec) obclient> |
我们可以看到oceanbase目前暂时不支持truncate partition。同时如果分区表存在全局index的话,也不允许进行drop partition操作。
当我们drop global index之后,可以成功drop partition。同时也可以看到,ob能够自动维护local 分区索引,其状态仍然是valid.
听说oceanbase 还支持invisible index(不可见索引),我知道这是Oracle 11g版本才引入的新特性。这里我们继续测试一下ob对于你invisible index的支持情况。
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 |
obclient> create table enmotech_t1 as select a from enmotech_part where 1=2; Query OK, 0 rows affected (0.06 sec) obclient> select count(1) from enmotech_t1; +----------+ | COUNT(1) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) obclient> set autocommit=on; Query OK, 0 rows affected (0.01 sec) obclient> insert into enmotech_t1 values(seq_enmotech_part.nextval); Query OK, 1 row affected (0.03 sec) ...... ...... obclient> show create table enmotech_T1 \G; *************************** 1. row *************************** TABLE: ENMOTECH_T1 CREATE TABLE: CREATE TABLE "ENMOTECH_T1" ( "A" NUMBER ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10 1 row in set (0.01 sec) ERROR: No query specified obclient> create index idx_a_enmotech_t1 on enmotech_t1(a) invisible; Query OK, 0 rows affected (0.42 sec) obclient> explain select * from enmotech_t1 where a=10 \G *************************** 1. row *************************** Query Plan: ========================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------ |0 |TABLE SCAN|ENMOTECH_T1|1 |43 | ========================================== Outputs & filters: ------------------------------------- 0 - output([ENMOTECH_T1.A]), filter([ENMOTECH_T1.A = 10]), access([ENMOTECH_T1.A]), partitions(p0) 1 row in set (0.00 sec) obclient> alter index idx_a_enmotech_t1 visible; 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 'index idx_a_enmotech_t1 visible' at line 1 obclient> obclient> alter table enmotech_t1 alter index idx_a_enmotech_t1 visible; Query OK, 0 rows affected (0.02 sec) obclient> obclient> explain select * from enmotech_t1 where a=11 \G; *************************** 1. row *************************** Query Plan: ============================================================= |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------------- |0 |TABLE SCAN|ENMOTECH_T1(IDX_A_ENMOTECH_T1)|0 |10 | ============================================================= Outputs & filters: ------------------------------------- 0 - output([ENMOTECH_T1.A]), filter(nil), access([ENMOTECH_T1.A]), partitions(p0) 1 row in set (0.00 sec) ERROR: No query specified |
可以看到,手工将index 设置为visible之后;优化器可以识别到index。不过大家可以可以看到,ob这个讲index设置为visible的操作非常别扭呀。。。
最后再次测试了unique index local,发现是支持的,可能是之前测试的表违反了唯一性条件,而ob的报错提示几乎没有,就提示依据failed。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
obclient> create table enmotech_part3(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), -> partition p_4 values less than (100), -> partition p_5 values less than (99999) -> ); Query OK, 0 rows affected (0.06 sec) obclient> create unique index idx_enmotech_part3_a on enmotech_part3 (a) local; Query OK, 0 rows affected (2.33 sec) obclient> |
可见,创建空表的情况下,unique local index是没任何问题的,因此此时没有重复数据。
+++函数索引支持测试
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 |
obclient> create table enmotech_t2 (a number ,b varchar2(100)); Query OK, 0 rows affected (0.05 sec) obclient> obclient> create index idx_function_t2 on enmotech_t2(substr(b,0,3)); Query OK, 0 rows affected (0.43 sec) obclient> insert into enmotech_t2 values(1,'aaaqe23r234q'); Query OK, 1 row affected (0.00 sec) obclient> insert into enmotech_t2 values(2,'bbbsdfaqe23r234q'); Query OK, 1 row affected (0.01 sec) obclient> insert into enmotech_t2 values(3,'cccsdfaqe23r234q'); Query OK, 1 row affected (0.00 sec) obclient> insert into enmotech_t2 values(4,'dddsdfaqe23r234q'); Query OK, 1 row affected (0.00 sec) obclient> insert into enmotech_t2 values(5,'ssssdfaqe23r234q'); Query OK, 1 row affected (0.01 sec) obclient> commit; Query OK, 0 rows affected (0.00 sec) obclient> select a,substr(b,0,3) from enmotech_T2; +------+-----------+ | A | SYS_NC18$ | +------+-----------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | | 4 | ddd | | 5 | sss | +------+-----------+ 5 rows in set (0.00 sec) obclient> explain select * from enmotech_t2 where substr(b,0,3)='aaa' \G; *************************** 1. row *************************** Query Plan: ========================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------ |0 |TABLE SCAN|ENMOTECH_T2|1 |40 | ========================================== Outputs & filters: ------------------------------------- 0 - output([ENMOTECH_T2.A], [ENMOTECH_T2.B]), filter([ENMOTECH_T2.SYS_NC18$ = 'aaa']), access([ENMOTECH_T2.B], [ENMOTECH_T2.SYS_NC18$], [ENMOTECH_T2.A]), partitions(p0) 1 row in set (0.01 sec) ERROR: No query specified |
从上面的执行计划来看似乎并没用到这个索引,仍然走了filter. 看来ob对于函数索引的支持还有一定缺陷。
+++是否支持位图索引
1 2 |
obclient> create bitmap index idx_bitmap_enmotech_t2 on enmotech_T2(a); 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 'bitmap index idx_bitmap_enmotech_t2 on enmotech_T2(a)' at line 1 |
从上面的测试来看,似乎语法都还不支持,因此应该是不支持bitmap index的。
最后我们来总结一下Oceanbase 2.2版本对于索引相关的支持情况:
1. 支持local 分区索引(包括local unique index)
2. 支持global 分区索引和非分区索引,但索引列必须是主键列或者包含分区前导列;
3. 不支持对分区表进行truncate操作;
4. 支持分区表的drop 操作.
5. 当分区表上存在global partition index时,不允许进行drop 分区操作;这一点跟oracle差别很大;
6. 对分区进行drop操作后,local 分区索引会自动维护,这一点跟oracle类似。
7. 支持函数索引,从执行计划看似乎无法使用,可能只是语法上支持,实质功能还有欠缺。
8. 不支持bitmap index。
9. 支持invisible index,对于index的visible操作语法跟Oracle有很大区别,比较别扭。
10. 从测试来看ob对于相关error提示非常不明确,比如前面测试的数据重复,创建unique index失败,没有太明显的提示,这方面有待加强。
Leave a Reply
You must be logged in to post a comment.