--- title: OceanBase 数据库中 EXISTS 的条件为单个常量导致执行慢的原因与解决方法-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 OceanBase 数据库中 EXISTS 的条件为单个常量导致执行慢的原因与解决方法相关的常见问题和使用技巧，帮助您快速解决 OceanBase 数据库中 EXISTS 的条件为单个常量导致执行慢的原因与解决方法的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # OceanBase 数据库中 EXISTS 的条件为单个常量导致执行慢的原因与解决方法 更新时间：2024-03-29 02:06 适用版本： V2.1.x、V2.2.x、V3.1.x、V3.2.x 内容类型：Troubleshoot ## 问题描述 OceanBase 数据库中 SQL 在 EXISTS 条件为单个常量时执行慢和耗时较高，而在 EXISTS 条件大于单个常量时执行快和耗时较低。 示例如下。 1. 创建表。 ```shell obclient [SYS]> CREATE TABLE "OB_EXISTS_TEST" ( "ID" NUMBER(10) CONSTRAINT "OB_EXISTS_TEST_OBNOTNULL_001" NOT NULL ENABLE, "TASK_ID" NUMBER(5), "CUS_ID" NUMBER(5), "CORE_CUSTOMER_NO" VARCHAR2(100), "GROUP_ID" NUMBER(10), "GROUP_NAME" VARCHAR2(100), "ROLE_ID" NUMBER(10), "ROLE_NAME" VARCHAR2(100), "AGENT_ID" NUMBER(5), "AGENT_NAME" VARCHAR2(100), "CALL_RESULT_STATUS" NUMBER(2) DEFAULT 3, "CUSTOMER_WILLINGNESS" NUMBER(2), "ORDER_START_TIME" DATE, "ORDER_END_TIME" DATE, "SUBMIT_STATE" NUMBER(2), "REMARKS" VARCHAR2(300), "CREATE_BY" VARCHAR2(100), "UPDATE_BY" VARCHAR2(100), "CREATE_TIME" DATE, "UPDATE_TIME" DATE, "CHANNEL_CODE" NUMBER(5), "CHANNEL_ID" NVARCHAR2(100), "CHANNEL_SUBMIT" NUMBER(5) DEFAULT 0, CONSTRAINT "SYS_C001" PRIMARY KEY ("ID")) COMPRESS FOR ARCHIVE REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0; Query OK, 0 rows affected (0.039 sec) ``` 2. 创建全局索引(共计四条全局索引)。 ```shell obclient [SYS]> CREATE INDEX "idx_core_customer_n01" on "OB_EXISTS_TEST" ("CORE_CUSTOMER_NO") GLOBAL ; Query OK, 0 rows affected (0.433 sec) ``` ```shell obclient [SYS]> CREATE INDEX "creat_time_index01" on "OB_EXISTS_TEST" ("CREATE_TIME") GLOBAL ; Query OK, 0 rows affected (0.426 sec) ``` ```shell obclient [SYS]> CREATE INDEX "idx_cusid_channel_id_task_id01" on "OB_EXISTS_TEST" ( "CUS_ID", "CHANNEL_ID", "TASK_ID") GLOBAL ; Query OK, 0 rows affected (0.428 sec) ``` ```shell obclient [SYS]> CREATE INDEX "create_ilme_index01" on "OB_EXISTS_TEST" ( "ID", "TASK_ID", "CUS_ID", "GROUP_NAME", "AGENT_ID", "AGENT_NAME", "CALL_RESULT_STATUS", "CUSTOMER_WILLINGNESS", "CREATE_TIME") GLOBAL ; Query OK, 0 rows affected (0.429 sec) ``` 3. 当 EXISTS 的条件为单个常量，执行如下 SQL。 ```shell obclient [SYS]> SELECT * FROM OB_MK_RESULT R WHERE EXISTS (SELECT id FROM (SELECT 491 AS ID FROM DUAL) TEMP WHERE TEMP.ID = R.ID); Empty set (1.024 sec) ``` 4. 展示 EXISTS 的条件为单个常量的 SQL 执行计划。 ```shell obclient [SYS]> EXPLAIN SELECT * FROM OB_MK_RESULT R WHERE EXISTS (SELECT id FROM (SELECT 491 AS ID FROM DUAL) TEMP WHERE TEMP.ID = R.ID); ``` 输出结果如下： ```shell +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ======================================== |ID|OPERATOR |NAME|EST. ROWS|COST | ---------------------------------------- |0 |SUBPLAN FILTER| |50000 |47007| |1 | TABLE SCAN |R |100000 |38681| |2 | LIMIT | |1 |1 | |3 | EXPRESSION | |1 |1 | ======================================== Outputs & filters: ------------------------------------- 0 - output([R.ID], [R.TASK_ID], [R.CUS_ID], [R.CORE_CUSTOMER_NO], [R.GROUP_ID], [R.GROUP_NAME], [R.ROLE_ID], [R.ROLE_NAME], [R.AGENT_ID], [R.AGENT_NAME], [R.CALL_RESULT_STATUS], [R.CUSTOMER_WILLINGNESS], [R.ORDER_START_TIME], [R.ORDER_END_TIME], [R.SUBMIT_STATE], [R.REMARKS], [R.CREATE_BY], [R.UPDATE_BY], [R.CREATE_TIME], [R.UPDATE_TIME], [R.CHANNEL_CODE], [R.CHANNEL_ID], [R.CHANNEL_SUBMIT]), filter([(T_OP_EXISTS, subquery(1))]), exec_params_([491 = R.ID]), onetime_exprs_(nil), init_plan_idxs_(nil) 1 - output([R.ID], [R.TASK_ID], [R.CUS_ID], [R.CORE_CUSTOMER_NO], [R.GROUP_ID], [R.GROUP_NAME], [R.ROLE_ID], [R.ROLE_NAME], [R.AGENT_ID], [R.AGENT_NAME], [R.CALL_RESULT_STATUS], [R.CUSTOMER_WILLINGNESS], [R.ORDER_START_TIME], [R.ORDER_END_TIME], [R.SUBMIT_STATE], [R.REMARKS], [R.CREATE_BY], [R.UPDATE_BY], [R.CREATE_TIME], [R.UPDATE_TIME], [R.CHANNEL_CODE], [R.CHANNEL_ID], [R.CHANNEL_SUBMIT]), filter(nil), access([R.ID], [R.TASK_ID], [R.CUS_ID], [R.CORE_CUSTOMER_NO], [R.GROUP_ID], [R.GROUP_NAME], [R.ROLE_ID], [R.ROLE_NAME], [R.AGENT_ID], [R.AGENT_NAME], [R.CALL_RESULT_STATUS], [R.CUSTOMER_WILLINGNESS], [R.ORDER_START_TIME], [R.ORDER_END_TIME], [R.SUBMIT_STATE], [R.REMARKS], [R.CREATE_BY], [R.UPDATE_BY], [R.CREATE_TIME], [R.UPDATE_TIME], [R.CHANNEL_CODE], [R.CHANNEL_ID], [R.CHANNEL_SUBMIT]), partitions(p0) 2 - output([491]), filter(nil), limit(1), offset(nil) 3 - output([1]), filter([?]) values({1}) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.003 sec) ``` 5. 将 EXISTS 改成 JOIN 方式，执行如下 SQL。 ```shell obclient [SYS]> SELECT * FROM OB_MK_RESULT R JOIN (SELECT ID FROM (SELECT 491 AS ID FROM DUAL)) TEMP ON TEMP.ID = R.ID; Empty set (0.003 sec) ``` 6. 展示 EXISTS 改成 JOIN 方式， SQL 的执行计划。 ```shell obclient [SYS]> EXPLAIN SELECT * FROM OB_MK_RESULT R JOIN (SELECT ID FROM (SELECT 491 AS ID FROM DUAL)) TEMP ON TEMP.ID = R.ID; ``` 输出结果如下： ```shell +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =================================================== |ID|OPERATOR |NAME|EST. ROWS|COST| --------------------------------------------------- |0 |NESTED-LOOP JOIN CARTESIAN| |1 |47 | |1 | TABLE GET |R |1 |46 | |2 | SUBPLAN SCAN |TEMP|1 |1 | |3 | EXPRESSION | |1 |1 | =================================================== Outputs & filters: ------------------------------------- 0 - output([R.ID], [R.TASK_ID], [R.CUS_ID], [R.CORE_CUSTOMER_NO], [R.GROUP_ID], [R.GROUP_NAME], [R.ROLE_ID], [R.ROLE_NAME], [R.AGENT_ID], [R.AGENT_NAME], [R.CALL_RESULT_STATUS], [R.CUSTOMER_WILLINGNESS], [R.ORDER_START_TIME], [R.ORDER_END_TIME], [R.SUBMIT_STATE], [R.REMARKS], [R.CREATE_BY], [R.UPDATE_BY], [R.CREATE_TIME], [R.UPDATE_TIME], [R.CHANNEL_CODE], [R.CHANNEL_ID], [R.CHANNEL_SUBMIT], [TEMP.ID]), filter(nil), conds(nil), nl_params_(nil) 1 - output([R.ID], [R.TASK_ID], [R.CUS_ID], [R.CORE_CUSTOMER_NO], [R.GROUP_ID], [R.GROUP_NAME], [R.ROLE_ID], [R.ROLE_NAME], [R.AGENT_ID], [R.AGENT_NAME], [R.CALL_RESULT_STATUS], [R.CUSTOMER_WILLINGNESS], [R.ORDER_START_TIME], [R.ORDER_END_TIME], [R.SUBMIT_STATE], [R.REMARKS], [R.CREATE_BY], [R.UPDATE_BY], [R.CREATE_TIME], [R.UPDATE_TIME], [R.CHANNEL_CODE], [R.CHANNEL_ID], [R.CHANNEL_SUBMIT]), filter(nil), access([R.ID], [R.TASK_ID], [R.CUS_ID], [R.CORE_CUSTOMER_NO], [R.GROUP_ID], [R.GROUP_NAME], [R.ROLE_ID], [R.ROLE_NAME], [R.AGENT_ID], [R.AGENT_NAME], [R.CALL_RESULT_STATUS], [R.CUSTOMER_WILLINGNESS], [R.ORDER_START_TIME], [R.ORDER_END_TIME], [R.SUBMIT_STATE], [R.REMARKS], [R.CREATE_BY], [R.UPDATE_BY], [R.CREATE_TIME], [R.UPDATE_TIME], [R.CHANNEL_CODE], [R.CHANNEL_ID], [R.CHANNEL_SUBMIT]), partitions(p0) 2 - output([TEMP.ID]), filter(nil), access([TEMP.ID]) 3 - output([491]), filter(nil) values({491}) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.003 sec) ``` 7. EXISTS 条件大于单个常量，如常量数为 2，执行如下 SQL。 ```shell obclient [SYS]> SELECT * FROM OB_MK_RESULT R WHERE EXISTS (SELECT ID FROM (SELECT 491 AS ID FROM DUAL UNION SELECT 504 AS ID FROM DUAL) TEMP WHERE TEMP.ID = R.ID); Empty set (0.004 sec) ``` 8. 展示 EXISTS 条件大于单个常量，如常量数为 2， SQL 的执行计划。 ```shell obclient [SYS]> EXPLAIN SELECT * FROM OB_MK_RESULT R WHERE EXISTS (SELECT ID FROM (SELECT 491 AS ID FROM DUAL UNION SELECT 504 AS ID FROM DUAL) TEMP WHERE TEMP.ID = R.ID); ``` 输出结果如下： ```shell +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =============================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------- |0 |NESTED-LOOP JOIN | |2 |84 | |1 | SUBPLAN SCAN |VIEW1|2 |2 | |2 | HASH UNION DISTINCT| |2 |2 | |3 | EXPRESSION | |1 |1 | |4 | EXPRESSION | |1 |1 | |5 | TABLE GET |R |1 |40 | =============================================== Outputs & filters: ------------------------------------- 0 - output([R.ID], [R.TASK_ID], [R.CUS_ID], [R.CORE_CUSTOMER_NO], [R.GROUP_ID], [R.GROUP_NAME], [R.ROLE_ID], [R.ROLE_NAME], [R.AGENT_ID], [R.AGENT_NAME], [R.CALL_RESULT_STATUS], [R.CUSTOMER_WILLINGNESS], [R.ORDER_START_TIME], [R.ORDER_END_TIME], [R.SUBMIT_STATE], [R.REMARKS], [R.CREATE_BY], [R.UPDATE_BY], [R.CREATE_TIME], [R.UPDATE_TIME], [R.CHANNEL_CODE], [R.CHANNEL_ID], [R.CHANNEL_SUBMIT]), filter(nil), conds(nil), nl_params_([VIEW1.TEMP.ID]) 1 - output([VIEW1.TEMP.ID]), filter(nil), access([VIEW1.TEMP.ID]) 2 - output([UNION([1])]), filter(nil) 3 - output([491]), filter(nil) values({491}) 4 - output([504]), filter(nil) values({504}) 5 - output([R.ID], [R.TASK_ID], [R.CUS_ID], [R.CORE_CUSTOMER_NO], [R.GROUP_ID], [R.GROUP_NAME], [R.ROLE_ID], [R.ROLE_NAME], [R.AGENT_ID], [R.AGENT_NAME], [R.CALL_RESULT_STATUS], [R.CUSTOMER_WILLINGNESS], [R.ORDER_START_TIME], [R.ORDER_END_TIME], [R.SUBMIT_STATE], [R.REMARKS], [R.CREATE_BY], [R.UPDATE_BY], [R.CREATE_TIME], [R.UPDATE_TIME], [R.CHANNEL_CODE], [R.CHANNEL_ID], [R.CHANNEL_SUBMIT]), filter(nil), access([R.ID], [R.TASK_ID], [R.CUS_ID], [R.CORE_CUSTOMER_NO], [R.GROUP_ID], [R.GROUP_NAME], [R.ROLE_ID], [R.ROLE_NAME], [R.AGENT_ID], [R.AGENT_NAME], [R.CALL_RESULT_STATUS], [R.CUSTOMER_WILLINGNESS], [R.ORDER_START_TIME], [R.ORDER_END_TIME], [R.SUBMIT_STATE], [R.REMARKS], [R.CREATE_BY], [R.UPDATE_BY], [R.CREATE_TIME], [R.UPDATE_TIME], [R.CHANNEL_CODE], [R.CHANNEL_ID], [R.CHANNEL_SUBMIT]), partitions(p0) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.004 sec) ``` 由示例可知 SQL 在 EXISTS 条件为单个常量时执行花费时间 1.024 sec，将 EXISTS 改成 JOIN 方式，SQL 执行花费时间 0.003 sec，当 EXISTS 条件大于单个常量，如常量数为 2 时，SQL 执行花费时间 0.004 sec。 ## 适用版本 OceanBase 数据库 V2.x 和 V3.x 版本。 ## 问题原因 OceanBase 数据库中 EXISTS 条件为单个常量时执行计划中 TABLE GET 算子未生成 NLJ 做 R 的 TABLE GET。但是没有条件下压，无法利用索引过滤数据，导致 SQL 在 EXISTS 条件为单个常量时执行慢和耗时较高。 ## 解决方法 - 升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库 V4.2.0 GA (oceanbase-4.2.0.0-100010082023083014) 及之后版本。 - 改写 SQL 将 EXISTS 改成 JOIN 方式。 - 在 SQL 中将 EXISTS 条件改为大于单个常量（如常量数为 2）。 上一篇 [优化器将 NOT EXISTS 改写成 Anti-Join 后变慢的原因？](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000475695) 下一篇 [OceanBase 数据库性能视图 gv$sql_audit 介绍](https://www.oceanbase.com/knowledge-base/oceanbase-database-20000000102) ![有帮助](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) 咨询热线