OceanBase索引&分区索引测试

李真旭 2020-06-10

3611

前面2篇文章重点测试了oceanbase 2.2版本对于分区的支持情况。这里重点测试一下索引和分区索引相关的内容。
首先来看下官方文档(2.1版本) 对于index 相关语法的介绍：

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索引

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>

我这里表实际上是有重复数据；看上去没有明显的提示。下面重建一个表定义好主键列再试试：

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支持全局分区索引吗？

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一致，首先你也指定分区键,同时引用的分区键必须是前导列。

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 是否也是这样呢？

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的支持情况。

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。

 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是没任何问题的，因此此时没有重复数据。
+++函数索引支持测试

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对于函数索引的支持还有一定缺陷。
+++是否支持位图索引

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版本对于索引相关的支持情况：

支持local 分区索引(包括local unique index)
支持global 分区索引和非分区索引,但索引列必须是主键列或者包含分区前导列;
不支持对分区表进行truncate操作；
支持分区表的drop 操作.
当分区表上存在global partition index时，不允许进行drop 分区操作；这一点跟oracle差别很大；
对分区进行drop操作后，local 分区索引会自动维护，这一点跟oracle类似。
支持函数索引，从执行计划看似乎无法使用，可能只是语法上支持，实质功能还有欠缺。
不支持bitmap index。
支持invisible index，对于index的visible操作语法跟Oracle有很大区别，比较别扭。
从测试来看ob对于相关error提示非常不明确，比如前面测试的数据重复，创建unique index失败，没有太明显的提示，这方面有待加强。

oceanbase

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

OceanBase索引&分区索引测试

评论