OceanBase 数据库中索引查询效率低于 Oracle 的问题

适用版本

OceanBase 数据库 V3.x 版本。

问题描述

相同 SQL 语句使用相同的索引访问，在 OceanBase 数据库中执行效率远远低于 Oracle。

场景描述：

• 表和索引的定义。

  obclient [SYS]> select * from dba_ind_columns where table_name='TEST1';
  

  +-------------+-------------+-------------+------------+---------------+-----------------+---------------+-------------+---------+
  | INDEX_OWNER | INDEX_NAME  | TABLE_OWNER | TABLE_NAME | COLUMN_NAME   | COLUMN_POSITION | COLUMN_LENGTH | CHAR_LENGTH | DESCEND |
  +-------------+-------------+-------------+------------+---------------+-----------------+---------------+-------------+---------+
  | HTZ         | IND_TEST1_1 | HTZ         | TEST1      | OBJECT_ID     |               1 |            22 |           . | ASC     |
  | HTZ         | IND_TEST1_1 | HTZ         | TEST1      | LAST_DDL_TIME |               2 |             7 |           . | ASC     |
  

  obclient [SYS]> desc htz.test1;
  

  +----------------+---------------+------+-----+---------+-------+
  | FIELD          | TYPE          | NULL | KEY | DEFAULT | EXTRA |
  +----------------+---------------+------+-----+---------+-------+
  | OWNER          | VARCHAR2(128) | YES  | NULL | NULL    | NULL  |
  | OBJECT_NAME    | VARCHAR2(128) | YES  | NULL | NULL    | NULL  |
  | SUBOBJECT_NAME | VARCHAR2(128) | YES  | NULL | NULL    | NULL  |
  | OBJECT_ID      | NUMBER(38)    | NO   | MUL | NULL    | NULL  |
  | DATA_OBJECT_ID | NUMBER        | YES  | NULL | NULL    | NULL  |
  | OBJECT_TYPE    | VARCHAR2(23)  | YES  | NULL | NULL    | NULL  |
  | CREATED        | DATE          | YES  | NULL | NULL    | NULL  |
  | LAST_DDL_TIME  | DATE          | YES  | MUL | NULL    | NULL  |
  | TIMESTAMP      | VARCHAR2(256) | YES  | NULL | NULL    | NULL  |
  | STATUS         | VARCHAR2(7)   | YES  | NULL | NULL    | NULL  |
  | TEMPORARY      | VARCHAR2(1)   | YES  | NULL | NULL    | NULL  |
  | GENERATED      | VARCHAR2(1)   | YES  | NULL | NULL    | NULL  |
  | SECONDARY      | VARCHAR2(1)   | YES  | NULL | NULL    | NULL  |
  | NAMESPACE      | NUMBER        | YES  | NULL | NULL    | NULL  |
  | EDITION_NAME   | VARCHAR2(128) | YES  | NULL | NULL    | NULL  |
  +----------------+---------------+------+-----+---------+-------+
  

• SQL 语句及执行计划。

  obclient [SYS]> select object_id,count(*) from htz.test1 
  where last_ddl_time=date '2022-02-27' group by object_id;
  

  +------------------+----------+
  | OBJECT_ID        | COUNT(*) |
  +------------------+----------+
  |       1.1006E+15 |        1 |
  +------------------+----------+
  1 row in set (0.373 sec)
  

  obclient [SYS]> explain select object_id,count(*) from htz.test1 where last_ddl_time=date '2022-02-27' group by object_id;
  

  +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
  +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | =============================================================
  |ID|OPERATOR            |NAME              |EST. ROWS|COST  |
  -------------------------------------------------------------
  |0 |EXCHANGE IN REMOTE  |                  |101      |211346|
  |1 | EXCHANGE OUT REMOTE|                  |101      |211314|
  |2 |  MERGE GROUP BY    |                  |101      |211314|
  |3 |   TABLE SCAN       |TEST1(IND_TEST1_1)|5617     |209771|
  =============================================================
  Outputs & filters: 
  -------------------------------------
  0 - output([TEST1.OBJECT_ID], [T_FUN_COUNT(*)]), filter(nil)
  1 - output([TEST1.OBJECT_ID], [T_FUN_COUNT(*)]), filter(nil)
  2 - output([TEST1.OBJECT_ID], [T_FUN_COUNT(*)]), filter(nil), 
  group([TEST1.OBJECT_ID]), agg_func([T_FUN_COUNT(*)])
  3 - output([TEST1.OBJECT_ID]), filter([TEST1.LAST_DDL_TIME = '2022-02-27 00:00:00']), 
  access([TEST1.LAST_DDL_TIME], [TEST1.OBJECT_ID]), partitions(p0)
  

该 SQL 在 OceanBase 数据库中执行时间为百毫秒级，在 Oracle 数据库中的执行时间为毫秒级。比较 OceanBase 数据库与 Oracle 的执行计划差异，发现虽然都是使用索引 IND_TEST1_1 进行访问，OceanBase 数据库中的访问方式为 TABLE SCAN（索引扫描），而 Oracle 中的访问方式为 INDEX SKIP SCAN。

问题原因

OceanBase 数据库 V4.0 版本之前不支持 INDEX SKIP SCAN，虽然使用索引 IND_TEST1_1 进行访问，确实全索引扫描，因此性能较差。

解决方法

为该查询语创建合适的索引，使用索引匹配查询，避免全索引扫描。

obclient> create index htz.ind_test1_2 on htz.test1(last_ddl_time,object_id);

再次执行 SQL 语句。

obclient [SYS]> select object_id,count(*) from htz.test1 where last_ddl_time=date '2022-02-27' group by object_id;

+------------------+----------+
| OBJECT_ID        | COUNT(*) |
+------------------+----------+
|       1.1006E+15 |        1 |
+------------------+----------+
1 row in set (0.078 sec)

执行计划如下。

obclient [SYS]> explain select object_id,count(*) from htz.test1 where last_ddl_time=date '2022-02-27' group by object_id;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ===========================================================
|ID|OPERATOR            |NAME              |EST. ROWS|COST|
-----------------------------------------------------------
|0 |EXCHANGE IN REMOTE  |                  |1        |37  |
|1 | EXCHANGE OUT REMOTE|                  |1        |37  |
|2 |  MERGE GROUP BY    |                  |1        |37  |
|3 |   TABLE SCAN       |TEST1(IND_TEST1_2)|1        |36  |
===========================================================
Outputs & filters: 
-------------------------------------
0 - output([TEST1.OBJECT_ID], [T_FUN_COUNT(*)]), filter(nil)
1 - output([TEST1.OBJECT_ID], [T_FUN_COUNT(*)]), filter(nil)
2 - output([TEST1.OBJECT_ID], [T_FUN_COUNT(*)]), filter(nil), 
group([TEST1.OBJECT_ID]), agg_func([T_FUN_COUNT(*)])
3 - output([TEST1.OBJECT_ID]), filter(nil), 
access([TEST1.OBJECT_ID]), partitions(p0)
|