--- title: 自定义函数在查询条件右侧时，不走索引-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 自定义函数在查询条件右侧时，不走索引相关的常见问题和使用技巧，帮助您快速解决 自定义函数在查询条件右侧时，不走索引的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 自定义函数在查询条件右侧时，不走索引 更新时间：2023-11-14 03:16 适用版本： V2.2.x、V3.1.x、V3.2.x 内容类型：Troubleshoot ## 问题描述 OceanBase 数据库中出现自定义函数在查询条件右侧时，不走索引的情况。 示例如下: 1. 创建测试表。 ```shell 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. 插入测试数据。 ```shell 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) ``` ```shell 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) ``` ```shell 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. 提交插入数据。 ```shell obclient [SYS]> COMMIT; Query OK, 0 rows affected (0.001 sec) ``` 4. 创建索引。 ```shell obclient [SYS]> CREATE INDEX idx_veridate ON t3 (VERI_DATE); Query OK, 0 rows affected (0.296 sec) ``` 5. 创建自定义函数。 ```shell 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. 执行查询测试语句。 ```shell 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 不走索引。 ```shell +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 语句逻辑后，可以走索引。 ```shell 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; ``` ```shell +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 并执行。 ```shell 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 测试语句。 ``` 输出执行计划如下。 可以看到自定义函数设置为 DETERMINISTIC 之后，SQL 走索引。 ```shell +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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) ``` #### 说明 - 由于第 5 步中自定义函数未设置为 DETERMINISTIC 则处在查询条件右侧时导致在第 6 步中 SQL 不走索引，在第 7 步中修改 SQL 逻辑后走索引。 - 在第 8 步中自定义函数设置为 DETERMINISTIC 之后在第 9 步中执行第 6 步中测试语句，SQL 走索引。 ## 适用版本 OceanBase 数据库 V2.x 和 V3.x 版本。 ## 问题原因 只要自定义函数不是定义为确定性的就不会抽 range，因为无法确定每次计算的结果是否一定相同。 ## 解决方法 - 将自定函数设置为 DETERMINISTIC 使 SQL 走索引。 - 修改 SQL 逻辑走索引。 上一篇 [分组聚合下压报错场景之一](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000270693) 下一篇 [优化器主动抽取 CTE 导致执行计划走偏，查询语句超时](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000340647) ![有帮助](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) 咨询热线