--- title: OceanBase 数据库中索引查询效率低于 Oracle 的问题-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 OceanBase 数据库中索引查询效率低于 Oracle 的问题相关的常见问题和使用技巧，帮助您快速解决 OceanBase 数据库中索引查询效率低于 Oracle 的问题的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # OceanBase 数据库中索引查询效率低于 Oracle 的问题 更新时间：2026-05-15 09:06 适用版本： V3.1.x、V3.2.x 内容类型：Troubleshoot ## 适用版本 OceanBase 数据库 V3.x 版本。 ## 问题描述 相同 SQL 语句使用相同的索引访问，在 OceanBase 数据库中执行效率远远低于 Oracle。 场景描述： - 表和索引的定义。 ```shell obclient [SYS]> select * from dba_ind_columns where table_name='TEST1'; ``` ```shell +-------------+-------------+-------------+------------+---------------+-----------------+---------------+-------------+---------+ | 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 | ``` ```shell obclient [SYS]> desc htz.test1; ``` ```shell +----------------+---------------+------+-----+---------+-------+ | 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 语句及执行计划。 ```shell obclient [SYS]> select object_id,count(*) from htz.test1 where last_ddl_time=date '2022-02-27' group by object_id; ``` ```shell +------------------+----------+ | OBJECT_ID | COUNT(*) | +------------------+----------+ | 1.1006E+15 | 1 | +------------------+----------+ 1 row in set (0.373 sec) ``` ```shell obclient [SYS]> explain select object_id,count(*) from htz.test1 where last_ddl_time=date '2022-02-27' group by object_id; ``` ```shell +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 进行访问，确实全索引扫描，因此性能较差。 ## 解决方法 为该查询语创建合适的索引，使用索引匹配查询，避免全索引扫描。 ```shell obclient> create index htz.ind_test1_2 on htz.test1(last_ddl_time,object_id); ``` 再次执行 SQL 语句。 ``` ```shell +------------------+----------+ | OBJECT_ID | COUNT(*) | +------------------+----------+ | 1.1006E+15 | 1 | +------------------+----------+ 1 row in set (0.078 sec) ``` 执行计划如下。 ``` ```shell +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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) | ``` Previous [如何通过 SQL AUDIT 确定 SQL 的远程计划执行节点](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002999213) Next [通过 gv$sql_audit 视图统计某段时间内的读写流量](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000254542) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线