如何在长文本上建索引

这个礼拜，我的直属领导老袁给我布置了几项任务，其中有一项就是让我去看一下如何在长文本上建立有效的索引？

如果数据量很大的情况下，直接在一个长文本上建索引肯定是不太现实的，因为数据量很大且文本太长的话，如果直接在上面建索引，索引的键值也就相当于是一个长文本，这样一来会浪费存储空间，二来oracle再去search索引的时候需要去一个一个键值的去比对长文本，这样效率必然是不高的！

怎么办？

我当天晚上坐公车回家的时候偶然想到oracle在shared pool里面找共享sql的执行计划的时候不是直接根据sql的文本去找的，而是先计算一个hash值，然后再根据这个hash值去检索shared pool。那其实我们这里可以照葫芦画瓢。

所以处理上述问题的大致思路是:

1、如果是要完全匹配，我们可以考虑在存长文本的时候同时调用一个公共的hash算法把这个长文本的hash值也存下来，然后在hash值上建索引。查找长文本的时候，先调用同样的hash算法把要检索的长文本的hash值算出来，然后再以这个hash值去检索数据库。

2、如果是要部分匹配，可以考虑用oracle的全文检索。

这篇文章只针对完全匹配这种情况。

公共的hash算法可以考虑调用oracle自带的函数DBMS_UTILITY.GET_HASH_VALUE，如下是这个函数的定义：

DBMS_UTILITY.GET_HASH_VALUE (

   name      VARCHAR2,

   base      NUMBER,

   hash_size NUMBER)

  RETURN NUMBER;

A hash value based on the input string. For example, to get a hash value on a string where the hash value should be between 1000 and 3047, use 1000 as the base value and 2048 as the hash_size value. Using a power of 2 for the hash_size parameter works best.

针对上述思路，有一些地方需要我们注意：

1、可以考虑在目标表上建insert trigger和update trigger，维护长文本的hash值就在上述两个trigger中实现，这样就可以不用改动前台DML操作的代码。

2、决定DBMS_UTILITY.GET_HASH_VALUE 中的hash_size的时候应该根据目标表的数据量来定，比如如果你的目标表的数据量有1000万，那么你的hash_size就应该是超过1000万的一个整数，且这个整数应该是2的n次方，比如2的24次方，也就是16777216。

3、前台针对长文本做完全匹配查询的时候，传到数据库之前将where条件中的

   长文本字段＝'用户输入的查询值'

   转变成

   存放hash值的字段 ＝DBMS_UTILITY.GET_HASH_VALUE('用户输入的查询值',1,16777216) ；

   如果前台传入的长文本字段的值是null，那应该将where条件中的

   长文本字段 is null

   转变成

   存放hash值的字段 ＝DBMS_UTILITY.GET_HASH_VALUE('',1,16777216)

4、如果你的数据库要升级，你应该事先考察一下不同的数据库版本中DBMS_UTILITY.GET_HASH_VALUE针对长文本算出来的hash值是否相同？如果不同，应该写运维程序去修改。据我测试，在9.2.0.6、10.2.0.1以及10.2.0.4这三个版本中DBMS_UTILITY.GET_HASH_VALUE的算法并未改变，如下所示：

SQL> conn dras/astca@astcatest;

Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0

Connected as dras

SQL> select DBMS_UTILITY.GET_HASH_VALUE('目标：为未来分摊、审核（或者高并发、高性能）转移到开放平台进行技术储备，AIX上C/C++程序设计能力；前端.NET应用借助于中间件（如taxedo）与unix上应用的通信；',1,16777216) hashvalue from dual;

 HASHVALUE

----------

   6101134

SQL> conn ipra/acca@ipradev;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as ipra

SQL> select DBMS_UTILITY.GET_HASH_VALUE('目标：为未来分摊、审核（或者高并发、高性能）转移到开放平台进行技术储备，AIX上C/C++程序设计能力；前端.NET应用借助于中间件（如taxedo）与unix上应用的通信；',1,16777216) hashvalue from dual;

 HASHVALUE

----------

   6101134

SQL> conn caipra/acca@ipratest;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

Connected as caipra

SQL> select DBMS_UTILITY.GET_HASH_VALUE('目标：为未来分摊、审核（或者高并发、高性能）转移到开放平台进行技术储备，AIX上C/C++程序设计能力；前端.NET应用借助于中间件（如taxedo）与unix上应用的通信；',1,16777216) hashvalue from dual;

 HASHVALUE

----------

   6101134