当前位置:首页 > Oracle > 正文内容

因函数索引逻辑调整造成查询结果不正确的案例分享

管理员2年前 (2018-08-08)Oracle442

收到数据仓库项目组人员提交的一个问题,说是某个查询结果的数据有问题,具体描述是在某一个列做了一个计算,并且作为一个查询条件放在了 where后面,得到了非预期结果,并且有截图(因数据涉密,这里不贴原图了),第一反应是,数据中存在着不可见字符(比如:空格),干扰了正常的结果,得到的回复是没有这样情况,在实际上跑了这个查询语句并查看了执行计划,发现了 INDEX RANGE SCAN 字样,即怀疑该列存在函数索引,随即再次沟通,说是在此做此查询之前,确实对该函数进行过一次逻辑上的调整。问题就此确定,解决的方法也比较简单,把函数索引删除重建即可。

我们知道,索引的创建,不仅仅是存储一个CREATE语句,而是把数据行的rowid和索引列的内容都存储到索引段中,函数索引也一样,也会根据函数的计算逻辑,把该列计算结果存储到索引段中,当函数的逻辑发生变化后,那么已经被索引的数据的,还是原来的值,新增的行才会按新的逻辑进行计算。

现将问题的发生及解决过程演示如下:

1、 构造一个测试表,没提供相应的测试数据

SQL> create table test(id int, name varchar2(30));
Table created
SQL> begin
  2    insert into test values(1, 'T1');
  3    insert into test values(2, 'At');
  4    insert into test values(3, 'F3');
  5    insert into test values(4, 'GL');
  6  end;
  7  /
PL/SQL procedure successfully completed

2、 创建自定义函数和函数索引

SQL> create or replace function fn_contain_digit(p_value varchar2)
  2  return int
  3  deterministic
  4  as
  5  begin
  6   return case when regexp_like(p_value,'[0-9]') then 1 else 0 end;
  7  end;
  8  /
Function created
SQL> create index ix_test_type on test(fn_contain_digit(name));
Index created

这是一个比较简单的函数,其功能是判断所提供的参数(VARCHAR2类型)中,是否包含数字,如包含则返回1,如不包含则返回0。

3、 检索数据

SQL> select * from test where fn_contain_digit(name) = 1;
        ID NAME
---------- --------------------
         1 T1
         3 F3

ID = 1 和ID = 3 的行,name 列都有数字,另外两行全部都是英文字母,不包含数字,这个结果没有问题。

4、 调整函数的逻辑后再次检索数据

SQL> create or replace function fn_contain_digit(p_value varchar2)
  2  return int
  3  deterministic
  4  as
  5  begin
  6   return case when regexp_like(p_value,'[0-9|a-f|A-F]') then 1 else 0 end;
  7  end;
  8  /
Function created
SQL> select id, name, fn_contain_digit(name) fn from test;
        ID NAME                                   FN
---------- -------------------- --------------------
         1 T1                                      1
         2 At                                      1
         3 F3                                      1
         4 GL                                      0
SQL> select * from test where fn_contain_digit(name) = 1;
        ID NAME
---------- --------------------
         1 T1
         3 F3

函数的功能进行了简单的调整, 原来只是判断是否包含数字,调整为可以包含A-F(小写为a-f)。第一个查询的结果中,我们关注一下ID = 2 的行,实际返回的结果是1,但是在第二个查询的结果中,并不包含它。

5、 删除交重建索引后再次确认

SQL> drop index ix_test_type;
Index dropped
SQL> create index ix_test_type on test(fn_contain_digit(name));
Index created
SQL> select * from test where fn_contain_digit(name) = 1;
        ID NAME
---------- --------------------
         1 T1
         2 At
         3 F3

重建索引后,问题解决。

6、 结论

如果表上存在函数索引,当函数的逻辑(文本内容)发生变化后,一定要把引用到的函数索引全部重新创建,才可以保证索引存储数据的正确性。

附:通过如下语句,可发查询到某个函数被哪些索引引用,FN_CONTAIN_DIGIT是函数的名称,要替换成自己的。

select NAME
  from user_dependencies
 where type = 'INDEX'
   and REFERENCED_NAME = 'FN_CONTAIN_DIGIT'
打赏 支付宝打赏 微信打赏
    扫描二维码至手机访问

    扫描二维码推送至手机访问。

    版权声明:本文由卖水果的net发布,如需转载请注明出处。

    转载请注明出处:http://www.msgde.net/oracle/oracle_function_index_need_recreate.html

    分享给朋友:

    发表评论

    访客

    ◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。