OceanBase 数据库中 EXISTS 的条件为单个常量导致执行慢的原因与解决方法

问题描述

OceanBase 数据库中 SQL 在 EXISTS 条件为单个常量时执行慢和耗时较高，而在 EXISTS 条件大于单个常量时执行快和耗时较低。

示例如下。

1. 创建表。

   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. 创建全局索引(共计四条全局索引)。

   obclient [SYS]> CREATE INDEX "idx_core_customer_n01" on "OB_EXISTS_TEST" ("CORE_CUSTOMER_NO") GLOBAL ;
   Query OK, 0 rows affected (0.433 sec)
   

   obclient [SYS]> CREATE INDEX "creat_time_index01" on "OB_EXISTS_TEST" ("CREATE_TIME") GLOBAL ;
   Query OK, 0 rows affected (0.426 sec)
   

   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)
   

   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。

   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 执行计划。

   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);
   

   输出结果如下：

   +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | 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。

   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 的执行计划。

   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;
   

   输出结果如下：

   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | 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。

   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 的执行计划。

   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);
   

   输出结果如下：

   +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | 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）。