informix 函数索引

创建方法：

CREATE FUNCTION toUpper( name VARCHAR(100) ) RETURNS VARCHAR(100)
    WITH (NOT VARIANT);

    RETURN upper( name );
END FUNCTION;

CREATE INDEX ucnameIndex ON t1 ( toUpper(name) );
SELECT * FROM t1 WHERE toUpper(name) LIKE 'ANTHONY % HOPKINS';

函数索引是根据用户定义例程（User Defined Routine，UDR）返回的值创建的。这里的 UDR 一词通常用来指代返回值的函数。UDR 必须是不可变的。也就是说，对于给定的参数，UDR 始终返回相同的值，而且 UDR 不能修改数据库或变量状态。和随机数生成程序、当前日期/时间函数一样，UDR 通常是变化的，因此不能用于函数索引。当定义 UDR 并将之用于函数索引时，您必须显式将其指定为 NOT VARIANT。

可以将 UDR 编写为一个存储过程语言（Stored Procedure Language，SPL）函数，或者使用外部语言（SQL、C/C++ 或 Java）编写为一个外部函数。

函数索引可以根据用户定义的类型创建。
函数索引可以使用以下任意一种访问方法：
B-树 （默认）
R-树
用户定义的二级方法

可针对单个列、单个列的派生值、多个列和多个列的派生值创建索引。针对多个列创建的索引称为复合索引（composite index）。例如，下面的查询针对一个列的列值和第二个列的派生值创建了一个复合索引：
CREATE INDEX idx1 ON myTable( c1, f(c3) );
 

函数索引的局限性

函数索引不能是内置的代数、指数、对数或十六进制函数。如果需要使用内置函数定义函数索引，那么必须从 SQL 或外部语言函数中调用该函数。

不能针对返回大对象的 UDR 创建函数索引。不允许使用大对象作为索引键，因为一般情况下不能够对大对象进行比较和排序。然而，需要注意，可以将大对象作为参数传递给 UDR。

如果将某个 UDR 用于函数索引，则该 UDR 不能使用集合数据类型作为参数类型。集合数据类型包括 SET、MULTISET 和 LIST。

对于用于函数索引的 UDR，传递给它的参数在数量上有所限制。根据所使用的 Informix Data Server (IDS) 版本以及实现 UDR 的具体语言的不同，这些限制也不尽相同。例如，对于 IDS 9.4，最多可以将 102 列作为参数传递给 C UDR，并且最多可以将 341 列作为参数传递给 Java 或 SPL UDR。有关此方面的详细内容，请参考您的文档

在创建或删除一个函数索引时，不能指定 ONLINE 关键字，如下所示：
CREATE INDEX ... ONLINE;  <== Not valid
DROP INDEX ... ONLINE;    <== Not valid

创建函数索引的注意事项

任何索引都存在开销。包括资源的使用和执行时间。所有索引都需要进行保存，并且，所有索引都需要制定执行时间并保存其键值。函数索引还会产生额外的函数执行开销。在创建函数索引时，必须针对表中的每一行执行相关的函数。并且，必须在 INSERT 和 UPDATE 操作期间执行函数。

在创建函数索引之前，总是需要对数据库进行详细的成本收益分析。分析的内容应该包括表中存储的数据量、执行数据查询的类型和频率。如果表非常小，或者不经常执行使用函数索引的查询，那么创建函数索引可能收效甚微。

 

部分细节可以参考：

https://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0712wilcox/