函数索引的局限性

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

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

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

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

比较函数索引和非函数索引

在创建和使用方面，函数索引和非函数索引之间存在着大量差异。

诸如UNIQUE和CLUSTER等索引选项，以及B-Tree、R-Tree等访问方法和用户定义的访问方法，可以同时应用于函数索引和非函数索引。同样，这两种类型的索引都可以指定一个 FILLFACTOR，并可以指定存储选项来控制创建索引的位置以及是否对索引进行分段。这两种索引都可以指定索引操作符类并按照降序或升序排列。

oncheck实用工具可以对两种类型的索引进行验证和修复。例如，下面的oncheck命令将对db数据库的tbl表中名为i1的索引进行验证：oncheck -ci db:tbl#i1。如果oncheck报告索引中存在一个问题，那么可以使用oncheck的-y选项进行修复。该选项可以同时对函数索引和非功能性索进行修复。

从用户的角度来看，函数索引和非功能性索之间的一个差异就是它们执行CREATE和DROP操作的方式不同。在创建或删除一个函数索引时，不能指定ONLINE关键字，如下所示：

以下是引用片段： CREATE INDEX … ONLINE;  <== Not valid DROP INDEX … ONLINE;    <== Not valid

            
　　这说明，在创建或删除函数索引时，执行索引的表中始终持有一个排他锁。在这段时间内，其他所有用户都无法访问这个表。

创建函数索引的注意事项

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

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

SQL EXPLAIN文件

查询计划被写入到SQL EXPLAIN文件中。下表展示了该文件的位置和名称。

平台IDS Server位置SQL EXPLAIN位置SQL EXPLAIN名称
　　UNIX本地 当前目录sqexplain.out
　　UNIX远程 远程计算机的主目录 sqexplain.out
　　Windows本地和远程 <INFORMIXDIR>/sqexpln <username>.out

现在，您已了解了函数索引的定义以及其使用方式和使用时机，接下来，将提供一些使用函数索引的例子。我们将为您提供分步指导，使您能够亲自实现函数索引。

示例

您已经了解了函数索引的定义以及其使用方式和使用时机。下面的例子将展示一些具体应用。每个例子都附带了分步指导，根据这些指导，您将能够亲自实现函数索引。

示例：对圆的面积执行函数索引

本示例展示如何对圆的面积创建函数索引。如果数据集较大，或者经常发出请求圆面积的查询，那么创建函数索引将有助于提高性能。

首先，创建一个表。

CREATE TABLE circles ( radius FLOAT );

 
　　接着，创建一个 SPL 函数，它将返回给定半径的圆的面积。

CREATE FUNCTION circleArea( radius FLOAT ) RETURNS float     WITH (NOT VARIANT);     RETURN 3.14159 * radius * radius; END FUNCTION;

对这个圆形区域创建一个函数索引。

CREATE INDEX areaOfCircleIndex on circles( circleArea( radius ) );

　　 
　　最后，执行查询，该查询将使用函数索引。DBMS 使用索引判断哪些元组（tuple）满足查询，并且仅将这些元组（tuple）作为查询结果返回。

SELECT radius FROM circle WHERE circleArea( radius ) < 500;

示例：对图像的平均RGB值执行函数索引

在这个例子中，我们将针对一副数字图像的像素平均色值创建函数索引。UDR 使用 C 编写，可以处理简单的彩色 TIFF 图像。提供了以下函数：

avgRGB：图像中所有像素的平均值
　　avgRed：图像中所有红色像素的平均值
　　avgGreen：图像中所有绿色像素的平均值
　　avgBlue：图像中所有蓝色像素的平均

　　这个例子演示了如何使用函数索引选择具有某些特征的图像。在创建函数索引时，将执行与其相关的 UDR 来对图像进行预处理，并将结果作为索引键保存。SELECT 查询在查询过滤器中指定图像处理函数，现在，这些查询的运行速度得到了提高，因为它们使用索引判断每个元组（tuple）是否满足查询。

设置

我们针对运行在64位Linux x86上的IDS version 11.10对该示例进行了测试。可针对不同平台修改相应的指令。要想了解您的平台适合哪些编译器和连接器，请参考 $INFORMIXDIR/incl/dbdk 中的 makeinc.* 文件。

本示例的源代码以及相关文件可下载获得。可通过 下载 一节下载代码。

设置以下环境变量：

INFORMIXDIR       the location of your IDS installation LD_LIBRARY_PATH   add $INFORMIXDIR/lib                   add $INFORMIXDIR/lib/esql

 
　　为智能二进制大对象（smart blob）创建 sbspace。注意：sbspace 名称必须与 ONCONFIG 文件中 SBSPACENAME 值匹配。在本例中，名为 sbsp2。

在与根dbspace相同的目录中，创建一个名为sbsp2的空文件（ONCONFIG 文件中 ROOTPATH 的值指定根 dbspace 的名称和位置）。

将文件所有权改为 informix:informix。
将文件权限改为 mode 660。

以用户 informix 的身份，创建 sbspace：

onspaces -c -S sbsp2 -p <fullpath>/sbsp2 -o 0 -s 50000

 
　　创建一个数据库，并将图像作为智能二进制大对象保存。loadImages.sql 脚本创建一个名为 imagedb 的数据库并加载一些图像。

cd <exampleDir> dbaccess – loadImages.sql

 
　　编译C UDR 并创建一个共享库。

cc -c -fPIC -I$INFORMIXDIR/incl/public imageUDR.c ld -shared -melf_x86_64 -Bsymbolic -o imageUDR.so imageUDR.o

 
　　注册C UDR。

根据您的 IDS 服务器的配置方式，您可能需要使用 EXTEND 角色创建 C UDR。如果您的服务器配置中的 IFX_EXTEND_ROLE 设置为 OFF，那么即使不具备 EXTEND 角色也可以创建 C UDR。如果服务器配置中的 IFX_EXTEND_ROLE 设置为 ON，则需要使用 EXTEND 角色才能创建 UDR。

您的数据库管理员可以使用下面的 SQL 命令向您授予 EXTEND 角色： GRANT EXTEND TO ‘<yourUser>’。

您可以使用下面的命令检查服务器配置：onstat -c。

将包含 C UDR 的共享库复制到 INFORMIXDIR：

Copy imageUDR.so to $INFORMIXDIR/extend Change the file permissions to mode 755

　　将 C UDR 注册到 Informix。注意，我们指定了 HANDLESNULLS，因此，当列值为 NULL 时，IDS 将允许函数返回一个值。

dbaccess imagedb – CREATE FUNCTION avgRGB( blob ) RETURNS INTEGER WITH ( NOT VARIANT， HANDLESNULLS ) EXTERNAL NAME ‘$INFORMIXDIR/extend/imageUDR.so( avgRGB )’ LANGUAGE C; CREATE FUNCTION avgRed( blob ) RETURNS INTEGER WITH ( NOT VARIANT， HANDLESNULLS ) EXTERNAL NAME ‘$INFORMIXDIR /extend/imageUDR.so( avgRed )’ LANGUAGE C; CREATE FUNCTION avgGreen( blob ) RETURNS INTEGER WITH ( NOT VARIANT， HANDLESNULLS ) EXTERNAL NAME ‘$INFORMIXDIR /extend/imageUDR.so( avgGreen )’ LANGUAGE C; CREATE FUNCTION avgBlue( blob ) RETURNS INTEGER WITH ( NOT VARIANT， HANDLESNULLS ) EXTERNAL NAME ‘$INFORMIXDIR /extend/imageUDR.so( avgBlue )’ LANGUAGE C;

 
验证是否已加载数据以及是否能够访问 C UDR：

dbaccess imagedb –     Database selected.     > select name，     > avgRed(image) as avgRed，     > avgGreen(image) as avgGreen，     > avgBlue(image) as avgBlue，     > avgRGB(image) as avgRGB     > from images;    …     name      yellowbluestainedglass     avgred    190     avggreen  190     avgblue   66     avgrgb    148     18 row(s) retrieved.     >

　　查询

发出使用 UDR 的查询。此时，不存在任何索引。set explain on 指示 IDS 生成显示查询计划的文件。

dbaccess imagedb – > set explain on; >  select name， >  avgRed(image) as avgRed， >  avgGreen(image) as avgGreen， >  avgBlue(image) as avgBlue， >  avgRGB(image) as avgRGB >  from images >  where avgRGB(image) > 150 and avgBlue(image) > 160; name      white avgred    255 avggreen  255 avgblue   255 avgrgb    255 name      redblue avgred    255 avggreen  0 avgblue   255 avgrgb    170 name      graygreentexture avgred    173 avggreen  173 avgblue   171 avgrgb    172 3 row(s) retrieved. >

　　检查 SQL EXPLAIN 输出。该查询计划显示了一个全表扫描。

以下是引用片段： QUERY: —— select name， avgRed(image) as avgRed， avgGreen(image) as avgGreen，     avgBlue(image) as avgBlue， avgRGB(image) as avgRGB from images where avgRGB(image) > 150 and avgBlue(image) > 160 Estimated Cost: 2 Estimated # of Rows Returned: 1   1) <owner>.images: SEQUENTIAL SCAN    <== Full Table Scan         Filters: (<owner>.avgblue(<owner>.images.image )> 160                      AND <owner>.avgrgb(<owner>.images.image )> 150 )  UDRs in query: ————–     UDR id  :       350     UDR name:       avgblue     UDR id  :       347     UDR name:       avgrgb     UDR id  :       347     UDR name:       avgrgb     UDR id  :       350     UDR name:       avgblue     UDR id  :       349     UDR name:       avggreen     UDR id  :       348     UDR name:       avgred

对 C UDR 提供的函数创建函数索引并再次发出查询：

以下是引用片段： dbaccess imagedb – >   create index avgRGBIndex on images( avgRGB( image ) ); >   create index avgRedIndex on images( avgRed( image ) ); >   create index avgGreenIndex on images( avgGreen( image ) ); >   create index avgBlueIndex on images( avgBlue( image ) ); >   set explain on; >   select name， >   avgRed(image) as avgRed， >   avgGreen(image) as avgGreen， >   avgBlue(image) as avgBlue， >   avgRGB(image) as avgRGB >   from images >   where avgRGB(image) > 150 and avgBlue(image) > 160;

　　检查 SQL EXPLAIN 输出。该查询计划展示了函数索引的使用。

QUERY: —— select name， avgRed(image) as avgRed， avgGreen(image) as avgGreen， avgBlue(image) as avgBlue， avgRGB(image) as avgRGB from images where avgRGB(image) > 150 and avgBlue(image) > 160 Estimated Cost: 1 Estimated # of Rows Returned: 2   1) <owner>.images: INDEX PATH    <== Index Scan         Filters: <owner>.avgblue(<owner>.images.image )> 160     (1) Index Keys: <owner>.avgrgb(image)   (Serial， fragments: ALL)         Lower Index Filter: <owner>.avgrgb(<owner>.images.image )> 150 UDRs in query: ————–     UDR id  :   350     UDR name:   avgblue     UDR id  :   347     UDR name:   avgrgb     UDR id  :   347     UDR name:   avgrgb     UDR id  :   350     UDR name:   avgblue     UDR id  :   349     UDR name:   avggreen     UDR id  :   348     UDR name:   avgred     UDR id  :   347     UDR name:   avgrgb

　　使用 SQL 指令实现查询计划。要详细了解与优化器相关的 SQL 指令，请参考 IDS 文档。

此处使用了 AVOID_INDEX 指令，指示查询优化器不要使用函数索引。

dbaccess imagedb – > set explain on; > select {+avoid_index(images avgrgbindex)} > * from images where avgrgb(image) > 100;

　　检查 SQL EXPLAIN 输出。该查询计划显示使用了 AVOID_INDEX 指令，并展示了全表扫描。

QUERY: —— select {+avoid_index(images avgrgbindex)} * from images where avgrgb(image) > 100 DIRECTIVES FOLLOWED: AVOID_INDEX ( images avgrgbindex ) DIRECTIVES NOT FOLLOWED: Estimated Cost: 2 Estimated # of Rows Returned: 6   1) <owner>.images: SEQUENTIAL SCAN    <== Full Table Scan         Filters: <owner>.avgrgb(<owner>.images.image )> 100 UDRs in query: ————–     UDR id  :   347     UDR name:   avgrgb