Functionbased Index and ORA01802

Abstract:

Function-based index 可以很巧妙地帮助我们实现“不一般”的unique index, 但是如果没有注意function-based index对于该index对应的function的要求，很有可能会触发ORA-01802这个错误。 本文尝试去阐述这个错误是如何触发的，以及如果去解决。

OK, let’s get down to business.

记得Tom大叔的力作《Oracle编程艺术》中提到function-based index 可以用来解决下面的问题。 比如说现在有个表Test, 其Schema 如下，

SQL> desc test;
Name                                      Null?    Type
----------------------------------------- -------- -----------------
ID                                                     NUMBER
NAME                                               VARCHAR2(20)

现在假设有这个需求，要求这个表只能存在一条Name为Frank的记录，对于其他的Name值则没有这个限制， 比如说Name列可以包含多个Fraud， Michael 等。这个该怎么解决呢？ 很显然，简单地在列Name上加上一个Unique索引是解决不了问题的，因为我们只是需要保证表中只包含一条Name是Frank的记录。提到Unique Index，我们很容易会想到它与Primary Key的区别就是它允许index entry为Null, 而且可以存在多条为Null的记录，因为Null 跟 Null 是不相等的（但是要注意，如果Unique Key包含两列以上，情况则不一样了！），因此可以认为是满足Unique Key的要求的。 那么如何来实现这个想法呢， 这时候我们要求助于function了，

create or replace function fnTestReplace(v_name in varchar2) return varchar2
as
begin
 return
 (
     case v_name
         when 'Frank' then v_name
         else NULL
     end
  );
end;
/

接下来，我们可以创建一个function-based index了，如下，

SQL> create unique index idx_test_1 on test(fnTestReplace(name));
create unique index idx_test_1 on test(fnTestReplace(name))
                                       *
ERROR at line 1:
ORA-30553: The function is not deterministic

可以看到，我们需要告诉 Oracle 该 function fnTestReplace 是 deterministic, 这样才能用该function来创建function-based index. 

create or replace function fnTestReplace(v_name in varchar2) return varchar2 deterministic
as
begin
 return
 (
     case v_name
         when 'Frank' then v_name
         else NULL
     end
  );
end;
/
/

SQL> create unique index idx_test_1 on test(fnTestReplace(name));

Index created.

好了，大功告成，接下来我们测试一下，

SQL> insert into test(id, name) values (1, 'Frank');

1 row created.

SQL> insert into test(id, name) values (1, 'Frank');
insert into test(id, name) values (1, 'Frank')
*
ERROR at line 1:
ORA-00001: unique constraint (FRANK.IDX_TEST_1) violated

SQL> insert into test(id, name) values (1, 'Fraud');

1 row created.

SQL> insert into test(id, name) values (1, 'Fraud');

1 row created.

SQL> insert into test(id, name) values (1, 'James');

1 row created.

SQL> insert into test(id, name) values (1, 'James');

1 row created.

可以看到这个结果是符合我们预期的！ 有了这个function-based index, 可以使得我们在Name这个列上创建unique index来保证Name为'Frank’的唯一性！

SQL> delete from test;

5 rows deleted.

SQL>

OK， 这个算是热身。 现在让我们来考虑如何解决下面这样一个需求：

现在要求对同一个ID只能存在一个值为‘Frank’ 的Name， 而对其他的值则没有这个要求。 乍看上去，跟上面的一个需求没什么多大不同。但是仔细一看，可以看出来这个需求是允许表Test存在多条值为"Frank”的记录的，只要对应的ID不一样就成。

那该怎么解决呢？ 显然，这次要创建的unique index除了要包含Name这一列，还需要包含ID这一列。创建如下index,

SQL> drop index idx_test_1;

Index dropped.

SQL> create unique index idx_test_2 on test(id, fnTestReplace(name));

Index created.

现在来测试一下，

SQL> insert into test(id, name) values(1, 'Frank');
insert into test(id, name) values(1, 'Frank')
*
ERROR at line 1:
ORA-00001: unique constraint (FRANK.IDX_TEST_2) violated

SQL> insert into test(id, name) values(2, 'Frank');

1 row created.

SQL> insert into test(id, name) values(2, 'Frank');
insert into test(id, name) values(2, 'Frank')
*
ERROR at line 1:
ORA-00001: unique constraint (FRANK.IDX_TEST_2) violated

So far so good! 可以看到，这个新的索引保证对于同一ID只能存在一条Name为Frank的记录。但是，what about the others?

SQL> insert into test(id, name) values(1, 'James');

1 row created.

SQL> insert into test(id, name) values(1, 'James');
insert into test(id, name) values(1, 'James')
*
ERROR at line 1:
ORA-00001: unique constraint (FRANK.IDX_TEST_2) violated

oh, no! 居然对其他的值不起作用了！ 这是因为现在unique index包含两列ＩＤ和Ｎａｍｅ，经过函数fnTestReplace的处理，这两条记录变成了

  1  NULL

  1  NULL

尽管Oracle认为两条记录为NULL不违反Unique约束，但是两条 1 – NULL 却是重复的！！因此，我们需要改变下函数fnTestReplace的处理策略了，不能把Name改成ＮＵＬＬ，而是需要把相同的Ｎａｍｅ改成不同的值。很容易想到我们可以借助Oracle Sequence来实现这个想法。

SQL> create sequence seq_test;

Sequence created.

 

然后把fnTestReplace改动如下：

 

create or replace function fnTestReplace(v_name in varchar2) return varchar2 deterministic
as
  v_seq varchar2(20);
begin
 select cast(seq_test.nextval as varchar2(20)) into v_seq from dual;
return
(
     case v_name
         when 'Frank' then v_name
         else v_seq
     end
  );
end;
/

现在来测试下，

SQL> select * from test;

        ID NAME
---------- --------------------
         1 Frank
         2 Frank
         1 James

SQL> insert into test (id, name) values (1, 'James');

1 row created.

SQL> insert into test (id, name) values (1, 'James');

1 row created.

SQL> insert into test (id, name) values (1, 'Frank');
insert into test (id, name) values (1, 'Frank')
*
ERROR at line 1:
ORA-00001: unique constraint (FRANK.IDX_TEST_2) violated

Wow！ 看起来一切都OK啦~！  但是这个时候如果我们尝试去删除一条记录，

SQL> delete from test where id = 1 and name = 'Frank';

1 row deleted.

SQL> delete from test where id = 1 and name = 'James';
delete from test where id = 1 and name = 'James'
            *
ERROR at line 1:
ORA-08102: index key not found, obj# 72525, file 6, block 44 (2)

可以看到，我们删除Name为"Frank”的记录是没有问题，但是删除Name为“James”的记录却出现了令人费解的错误！查看Oracle文档，可以看到这个ORA-08102是个Oracle内部错误，给出的解决方案是求助于Oracle Support人员！这个太有点扯了！ 显然，我们这里遇到的问题，没必要要找Oracle Support的，因为很显然问题就出现我们创建的那个Function-based index了，更具体一点应该是function fnTestReplace出现了些什么问题。 

让我们换个角度来思考问题， 为什么往表里面插入数据都正常，而在删除数据的时候（只是对Name不是Frank的记录）才有这个问题发生呢？ 还有这个Error的信息表明是index key找不到，先不管index key是什么东东，但是Oracle肯定是需要在删除表中的数据同时去删除Index 对应的entry的。 因为，我们的index是经过function处理的，因此index entry中存在的值跟表中的数据是不一样，可想而知Oracle在删除表中数据的时候也需要根据我们的函数fnTestReplace来得到该条记录对应于index entry的哪一条。

于是，这时候问题出现了，Oracle找不到了对应于表中数据的index entry了！这个很诡异了，index entry是Oracle自己放进去的，现在它自己居然找不到了，这不是太不像话了嘛！

但果真如此吗？ 还记得function-based index对函数的要求吗？是deterministic! 而fnTestReplace确实是deterministic的吗？虽然在函数中加上了这个属性，但是这只是表象，是我们骗过了Oracle。现在我们来仔细看下这个函数的定义，

create or replace function fnTestReplace(v_name in varchar2) return varchar2 deterministic
as
  v_seq varchar2(20);
begin
 select cast(seq_test.nextval as varchar2(20)) into v_seq from dual;
return
(
     case v_name
         when 'Frank' then v_name
         else v_seq
     end
  );
end;
/

可以看到，对于Name为非Frank的时候，得到的值是不确定的，同样的输入得到的输出是不同的，而这正是我们用sequence的目的所在！很显然，这与deterministic是不符的！这也难怪，当Oracle在删除数据的时候，用同样的Name却得到不同的index entry，这也难怪它会抱怨index key not found! 

分析到这里，一切都水落石出了，function fnTestReplace是罪魁祸首！ 但是，我们不借助这个sequence, 如果保证index entry的唯一性呢？ 可以换个思路，这次我们把Index entry 变成一列，但是每个index entry仍然与ＩＤ和 Name相关， 当Name为Frank的时候，我们让这个Index Entry为ID 和 Name的组合，　否则这个Index entry为 NULL， 这样我们可以保证对于每个ID只能存在一条Name为Frank的记录，而对于其他Name则没有这个限制。

所以，Function fnTestReplace变成如下，

create or replace function fnTestReplace(n_id in number, v_name in varchar2) return varchar2 deterministic
as
begin
return
(
     case v_name
         when 'Frank' then n_id || v_name
         else NULL
     end
  );
end;
/

现在函数的参数变成两个了， 分别对应ID和Name两列。同样地，Index也要变化，

SQL> create unique index idx_test_3 on test(fnTestReplace(id, name));

Index created.

SQL>

现在来测试下：

SQL> insert into test(id, name) values(1, 'Frank');

1 row created.

SQL> insert into test(id, name) values(1, 'Frank');
insert into test(id, name) values(1, 'Frank')
*
ERROR at line 1:
ORA-00001: unique constraint (FRANK.IDX_TEST_3) violated

SQL> insert into test(id, name) values(2, 'Frank');

1 row created.

SQL> insert into test(id, name) values(2, 'Frank');
insert into test(id, name) values(2, 'Frank')
*
ERROR at line 1:
ORA-00001: unique constraint (FRANK.IDX_TEST_3) violated

SQL> insert into test(id, name) values(2, 'James');

1 row created.

SQL> insert into test(id, name) values(2, 'James');

1 row created.

SQL> delete from test where id = 1 and name = 'Frank';

1 row deleted.

SQL> delete from test where id = 2 and name = 'James';

2 rows deleted.

SQL>

可以看到，现在一切都ＯＫ了！！

小结一下，　灵活运行function-based index 可以给我们带来意想不到的效果，但是一定要理解deterministic 函数的derterministic实质意义！

-------The End------