自定义函数在查询条件右侧时，不走索引

问题描述

OceanBase 数据库中出现自定义函数在查询条件右侧时，不走索引的情况。

示例如下:

1. 创建测试表。

   obclient [SYS]> CREATE TABLE "T3" (
       "ID" NUMBER(38),
       "NAME" VARCHAR2(100),
       "NAME2" VARCHAR2(100),
       "VERI_DATE" VARCHAR2(100)
      ) COMPRESS FOR ARCHIVE REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0;
    Query OK, 0 rows affected (0.032 sec)
   

2. 插入测试数据。

   obclient [SYS]> INSERT INTO T3 (ID,NAME, NAME2, VERI_DATE) VALUES(1, 'HDHDH', 'DDDDD', '2023-09-21');
   Query OK, 1 row affected (0.012 sec)
   

   obclient [SYS]> INSERT INTO T3 (ID,NAME, NAME2, VERI_DATE) VALUES(2, 'HDHDDDH', 'DDDDDDD', '2023-09-22');
   Query OK, 1 row affected (0.000 sec)
   

   obclient [SYS]> INSERT INTO T3 (ID,NAME, NAME2, VERI_DATE) VALUES(3, 'HDHDDADDH', 'DDDDDDDSD', '2023-09-23');
   Query OK, 1 row affected (0.001 sec)
   

3. 提交插入数据。

   obclient [SYS]> COMMIT;
   Query OK, 0 rows affected (0.001 sec)
   

4. 创建索引。

   obclient [SYS]> CREATE INDEX idx_veridate ON t3 (VERI_DATE);
   Query OK, 0 rows affected (0.296 sec)
   

5. 创建自定义函数。

   obclient [SYS]> CREATE OR REPLACE FUNCTION fun_to_date(i_string IN VARCHAR2,i_format IN VARCHAR2) RETURN DATE IS
    BEGIN
    RETURN to_date(i_string, i_format);
    EXCEPTION
    WHEN OTHERS THEN
    RETURN NULL;
    END fun_to_date;
    /
   Query OK, 0 rows affected (0.068 sec)
   

6. 执行查询测试语句。

   obclient [SYS]> explain select * from t3 where VERI_DATE = TO_CHAR((to_date(fun_to_date('20230921','YYYYMMDD')-6),'YYYY-MM-DD');
   

   输出执行计划如下。 可以看到由于第 5 步中自定义函数未设置为 DETERMINISTIC 则处在查询条件右侧时导致 SQL 不走索引。

   
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | ===============================================
   |ID|OPERATOR  |NAME            |EST. ROWS|COST|
   -----------------------------------------------
   |0 |TABLE SCAN|T3              |1        |92  |
   ===============================================
   
   Outputs & filters:
   -------------------------------------
   0 - output([T3.ID], [T3.NAME], [T3.NAME2], [T3.VERI_DATE]), filter([T3.VERI_DATE = TO_CHAR(SYS.FUN_TO_DATE('20230921', 'YYYYMMDD') - 6, 'YYYY-MM-DD')]), 
      access([T3.VERI_DATE], [T3.ID], [T3.NAME], [T3.NAME2]), partitions(p0)
   |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.006 sec)
   

7. 修改 SQL 语句逻辑后，可以走索引。

   obclient [SYS]> explain select * from t3 a join (select TO_CHAR((fun_to_date('20230921','YYYYMMDD')-6),'YYYY-MM-DD') VERI_DATE1 from dual) b on a.VERI_DATE = b.VERI_DATE1;
   

   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | ===============================================
   |ID|OPERATOR        |NAME           |EST. ROWS|COST |
   -----------------------------------------------------
   |0 |NESTED-LOOP JOIN|               |1        |46   |
   |1 | SUBPLAN SCAN   |B              |1        |1    |
   |2 |  EXPRESSION    |               |1        |1    |
   |3 | TABLE SCAN     |A(IDX_VERIDATE)|1        |46   |
   =====================================================
   Outputs & filters: 
   -------------------------------------
   0 - output([A.ID], [A.NAME], [A.NAME2], [A.VERI_DATE], [B.VERI_DATE1]), filter(nil), 
      conds(nil), nl_params_([B.VERI_DATE1])
   1 - output([B.VERI_DATE1]), filter(nil), 
      access([B.VERI_DATE1])
   2 - output([TO_CHAR(SYS.FUN_TO_DATE('20110107', 'YYYYMMDD') - 6, 'YYYY-MM-DD')]), filter(nil)
      values({TO_CHAR(SYS.FUN_TO_DATE('20110107', 'YYYYMMDD') - 6, 'YYYY-MM-DD')})
   3 - output([A.VERI_DATE], [A.ID], [A.NAME], [A.NAME2]), filter(nil), 
      access([A.VERI_DATE], [A.ID], [A.NAME], [A.NAME2]), partitions(p0)
   |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.006 sec)
   

8. 自定义函数设置为 DETERMINISTIC 并执行。

   obclient [SYS]> CREATE OR REPLACE FUNCTION fun_to_date(i_string IN VARCHAR2, i_format IN VARCHAR2)
   RETURN DATE DETERMINISTIC is
   BEGIN
   RETURN to_date(i_string, i_format);
   EXCEPTION WHEN OTHERS THEN RETURN NULL;
   END fun_to_date;
   /
   Query OK, 0 rows affected (0.061 sec)
   

9. 执行步骤 6 测试语句。

   obclient [SYS]> explain select * from t3 where VERI_DATE = TO_CHAR((to_date(fun_to_date('20230921','YYYYMMDD')-6),'YYYY-MM-DD');
   

   输出执行计划如下。 可以看到自定义函数设置为 DETERMINISTIC 之后，SQL 走索引。

   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | ===============================================
   |ID|OPERATOR  |NAME            |EST. ROWS|COST|
   -----------------------------------------------
   |0 |TABLE SCAN|T3(IDX_VERIDATE)|1        |92  |
   ===============================================
   
   Outputs & filters:
   -------------------------------------
   0 - output([T3.ID], [T3.NAME], [T3.NAME2], [T3.VERI_DATE]), filter(nil),
      access([T3.VERI_DATE], [T3.ID], [T3.NAME], [T3.NAME2]), partitions(p0)
   |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.025 sec)
   

适用版本

OceanBase 数据库 V2.x 和 V3.x 版本。

问题原因

只要自定义函数不是定义为确定性的就不会抽 range，因为无法确定每次计算的结果是否一定相同。

解决方法

• 将自定函数设置为 DETERMINISTIC 使 SQL 走索引。

• 修改 SQL 逻辑走索引。