在Informix中创建并使用函数索引

　　随着数据量以惊人速度不断增长，数据库管理系统将继续关注性能问题。本文主要介绍一种名为函数索引（functional index）的性能调优技术。根据数据库使用情况的统计信息创建并使用函数索引，可以显著提升 SELECT 查询的性能。通过本文了解如何在 IBM ® Informix ® Dynamic Server 中创建和使用函数索引并最大限度提升查询性能。

　　简介

　　在选择数据库管理系统（DBMS）时，性能是一个关键的考虑因素。在执行 SELECT、INSERT、UPDATE 和 DELETE 操作时，很多因素都会对性能产生影响。这些因素包括：

　　持久性数据存储的速度和大小

　　数据存储结构

　　数据访问方法

　　随着数据集不断变大，查询性能愈发变得重要。

　　通常，使用索引可以改善查询性能。索引将数据库中的行位置与一组有序数据子集和/或数据派生物关联在一起。索引可以减少 DBMS 在执行查询时检查的行（或 元组）数量，从而获得性能增益。有时，仅通过搜索索引即可完成查询，而不需要从表中取回任何元组（tuple）。例如，如果您在列 c1 中建有索引，并且发出查询 select c1 from t1 where c1 < 10 ，那么索引中包含了可以满足查询的所有信息。

　　有趣的是，ANSI SQL 标准并没有说明如何创建、实现或维护索引。因此，数据库供应商可以按照自己的方式自由地实现索引。

　　本文讨论了 Informix Dynamic Server 的函数索引特性。要理解本文涉及的概念，您需要熟悉基本的数据库术语和概念，例如模式、表、行、列、索引和可扩展性。还需了解 Informix Dynamic Server (IDS) 的基本配置以及如何启动和停止服务器、如何使用 ONCONFIG 文件进行配置。此外，还需熟悉基本的 SQL 命令以及如何使用 dbaccess 对服务器执行 SQL 命令。

　　本文的目的是帮助您理解函数索引的定义以及使用。此外，您还将了解如何创建和使用函数索引，以及在创建函数索引之前需要考虑的一些问题。

　　函数索引的优势

　　索引按照某种顺序保存列值。函数索引对列中的数据进行转换并按照顺序保存转换后的值。

　　假设某个表中保存了一个企业的员工名称，并且需要保留名称的大小写形式。那么，如果查询需要执行大小写不敏感的搜索（如下所示），则必须转换数据：

SELECT * FROM t1 WHERE toUpper(name) LIKE 'ANTHONY % HOPKINS';

　　如果没有为名称建立索引，那么 DBMS 将执行全表扫描并对每个元组（tuple）的 name 列应用 toUpper 函数。要确定元组（tuple）是否满足查询，必须调用 toUpper 函数。当表非常大或者大量会话发出这种类型的查询时，性能将会有所下降。

　　避免调用 toUpper 函数的一种方法是在表中同时保存大小写混合的名称和大写名称。应用程序查询大小写不敏感的列：

SELECT * FROM t1 WHERE ucname like 'ANTHONY % HOPKINS';

　　如果没有为 ucname 创建索引，DBMS 仍然执行全表扫描，但是不会对数据进行进一步处理来判断其是否满足查询。尽管这样做改善了性能，但并不是理想的解决方案，因为表非常大，而且所有需要操纵或访问数据的应用程序必须包括处理 ucname 的逻辑。

　　改善查询性能的一种更好的方法是对 name 创建函数索引：

　　CREATE FUNCTION toUpper( name VARCHER(100) ) RETURNS VARCHAR(100)
　　　　WITH (NOT VARIANT);
　　　　RETURN upper( name );
　　END FUNCTION;
　　CREATE INDEX ucnameIndex ON t1 ( toUpper(name) );

　　当执行这种查询时，DBMS 可以使用函数索引判断哪些元组元组（tuple） 满足查询。DBMS 只获取并返回这些满足查询的元组（tuple），如下面的清单所示：

SELECT * FROM t1 WHERE toUpper(name) LIKE 'ANTHONY % HOPKINS';

　　DBMS 将自动管理函数索引以及不需要包含逻辑来管理大写形式数据的应用程序。通过使用 INSERT、UPDATE 和 DELETE 操作对索引进行更新，DBMS 能够确保索引始终与表数据一致。

　　接下来将深入讨论如何创建和使用函数索引，以及如何生成和检验查询计划，并提供具体的示例。

　　各种类型的函数索引

　　函数索引是根据用户定义例程（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。有关此方面的详细内容，请参考您的文档。

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

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

　　诸如 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 文件中。下表展示了该文件的位置和名称。

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

　　示例

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

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

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

　　首先，创建一个表。

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

　　示例：使用 R-Tree 访问方法的函数索引

　　在本例中，我们将向您展示如何创建使用 R-Tree 访问方法的函数索引。R-Tree 数据结构可以保存有关多维数据的信息，并用于有关空间或地理数据的索引。这类索引有助于改善针对空间数据的查询的性能。

　　要理解本示例，需要熟悉 IDS DataBlade 技术。具体来说，您应该能够安装和配置所使用的 Spatial DataBlade。有关 DataBlade 的详细信息，请参考 IBM Informix Spatial DataBlade 用户指南。

　　本例使用的数据包括一些城市的地理数据和 Oregon 州的海啸影响范围。本文下载部分没有提供这些地理数据，但是可以通过 Oregon Geospatial Enterprise Office 网站 自由获取。可从该站点下载 City Limits 2007 和 Tsunami Inundation Line 数据集。

　　假设您希望查找即将受海啸影响的城市。为此，发出一个空间数据查询，搜索其中心包含在海啸线内的所有城市。要满足这个查询，必须计算地理城市中心，因此，需要创建一个函数索引来计算这个值。由于涉及到空间数据，您需要为这个索引指定 R-Tree 访问方法。

　　下面列出了 R-Tree 函数索引的一些有趣特性：

　　它们不仅是针对相交区域的索引；叶页（leaf page）面保存数据对象本身。

　　R-Tree 具有高度上的平稳性：

　　从根页面到任何叶页面的路径均穿过相同数量的层。

　　所有 leave 节点都位于同一级别。

　　设置

　　本例中使用的空间函数来自 Spatial DataBlade。因此，创建和使用索引不需要编写额外的代码。

　　该示例针对运行在 64 位 Linux x86 上的 IDS version 11.10 进行了测试。可针对不同平台对指令进行相应修改。

　　设置以下环境变量：

INFORMIXDIR　　 the location of your IDS installation
PATH　　　　　　add $INFORMIXDIR/bin
　　　　　　　　add $INFORMIXDIR/extend/<spatialDataBladeDir>/bin (see below)

　　安装 Spatial DataBlade。安装指导可从 IDS 11.10 信息中心获得（参考本文末尾的链接）。

　　下面的指令假设您已经在默认位置安装好了 Spatial DataBlade version 8.21： $INFORMIXDIR/extend/spatial.8.21.xC1，其中 ‘x’ 表示特定于平台的字母。对于 64 位 Linux x86，默认位置为 $INFORMIXDIR/extend/spatial.8.21.FC1。可根据您的环境对指令作出相应修改。

　　为空间数据创建 sbspace。注意：sbspace 名必须与 ONCONFIG 文件中的 SYSSBSPACENAME 值匹配。本例假设 sbspace 名为 syssbspace。

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

　　将文件所有权改为 informix:informix。

　　将文件权限改为 mode 660。

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

onspaces -c -S syssbspace -p <fullpath>/syssbspace -o 0 -s 100000

　　创建您的数据库。

dbaccess - -
create database spatialdb;

　　注册 Spatial DataBlade。

　　使用 blademgr 实用工具注册 Spatial DataBlade。Spatial DataBlade 依赖于 R-Tree DataBlade，后者由 IDS 自动附带。因此，必须同时向您的数据库注册这两个 DataBlade。

blademgr
register ifxrltree.2.00 spatialdb
register spatial.8.21.FC1 spatialdb
list spatialdb
　　DataBlade modules registered in database spatialdb:
　　　　ifxrltree.2.00　　　　spatial.8.21.FC1

　　加载空间数据。

　　从 City Limits 2007 和 Tsunami Inundation Line 下载中提取文件，并分别保存在两个目录中，即 citylim_2007 和 tsunami：

　　　　　　　　　　　　ls citylim_2007
citylim_2007.dbf
citylim_2007.prj
citylim_2007.sbn
citylim_2007.sbx
citylim_2007.shp
citylim_2007.shp.xml
citylim_2007.shx
ls tsunami
PRJdevelopment.pdf
tsunami.dbf
tsunami.html
tsunami.pdf
tsunami.prj
tsunami.shp
tsunami.shp.xml
tsunami.shx

　　注意：在 UNIX 上，数据文件名称区分大小写，而且必须为小写。这是 Shapefile 数据的要求。如果文件名不正确，那么 loadshp 实用工具将报告错误，指出它无法找到数据文件。

　　关于如何正确配置投影数据超出本文讨论的范围。如果没有正确配置，loadshp 实用工具在尝试处理投影信息时将返回错误，因此，隐藏或删除扩展名为 'prj' 的文件：

rm citylim_2007/citylim_2007.prj
rm tsunami/tsunami.prj

　　使用 Spatial DataBlade 附带的 loadshp 实用工具将数据加载到 IDS 中。该实用工具位于 $INFORMIXDIR/extend/spatial.8.21.FC1/bin 中。 loadshp 创建了两个表：cities 和 tsunami，并将空间数据分别加载到 citylim 和 inundation 列中。

$INFORMIXDIDR/extend/spatial.8.21.FC1/bin/loadshp -o create -D spatialdb2
　　-l cities,citylim -f <fullpath>/citylim_2007
$INFORMIXDIDR/extend/spatial.8.21.FC1/bin/loadshp -o create -D spatialdb2
　　-l tsunami,inundation -f <fullpath>/tsunami

　　查询

　　发出查询。在这个查询中，

　　st_intersects、st_centroid 和 st_envelope 由 Spatial DataBlade 提供

　　st_envelope 计算每条海啸线的边界，返回类型 st_multilinestring

　　st_centroid 计算每个城市的中心，返回类型 st_multipolygon

　　st_intersects 可确定两种结构是否相互交叉

　　这次没有定义函数索引。

　　dbaccess spatialdb -
　　set explain on;
　　select city_name from cities, tsunami
　　where st_intersects( st_centroid( citylim ), st_envelope( inundation ) );
　　city_name　　　　　　　　　　　
　　Astoria　　　　　　　　　　　
　　Bandon　　　　　　　　　　　　
　　Bay City　　　　　　　　　　　
　　Brookings　　　　　　　　　　
　　Cannon Beach　　　　　　　　　
　　Cave Junction　　　　　　　　
　　Coos Bay　　　　　　　　　　　
　　Coquille　　　　　　　　　　　
　　Depoe Bay　　　　　　　　　　
　　Dunes City　　　　　　　　　　
　　Elkton　　　　　　　　　　　　
　　Falls City　　　　　　　　　　
　　Florence　　　　　　　　　　　
　　Garibaldi　　　　　　　　　　
　　Gearhart　　　　　　　　　　　
　　Glendale　　　　　　　　　　　
　　Gold Beach　　　　　　　　　　
　　Grants Pass　　　　　　　　　
　　Lakeside　　　　　　　　　　　
　　Lincoln City　　　　　　　　　
　　Manzanita　　　　　　　　　　
　　Myrtle Point　　　　　　　　　
　　Nehalem　　　　　　　　　　　
　　Newport　　　　　　　　　　　
　　North Bend　　　　　　　　　　
　　Port Orford　　　　　　　　　
　　Powers　　　　　　　　　　　　
　　Reedsport　　　　　　　　　　
　　Riddle　　　　　　　　　　　　
　　Rockaway Beach　　　　　　　　
　　Seaside　　　　　　　　　　　
　　Siletz　　　　　　　　　　　　
　　Tillamook　　　　　　　　　　
　　Toledo　　　　　　　　　　　　
　　Waldport　　　　　　　　　　　
　　Warrenton　　　　　　　　　　
　　Wheeler　　　　　　　　　　　
　　Willamina　　　　　　　　　　
　　Winston　　　　　　　　　　　
　　Yachats　　　　　　　　　　　
　　40 row(s) retrieved.

　　检查 SQL EXPLAIN 输出。该查询计划显示了全表扫描。针对每一个元组（tuple）执行 st_centroid() 计算。

QUERY:
------
select city_name from cities, tsunami
where st_intersects( st_centroid( citylim ), st_envelope( inundation ) )
Estimated Cost: 25671
Estimated # of Rows Returned: 7018
　1) <owner>.tsunami: SEQUENTIAL SCAN　　<== Full Table Scan
　2) <owner>.cities: SEQUENTIAL SCAN　　 <== Full Table Scan
　　　　Filters: informix.st_intersects(informix.st_centroid(<owner>.cities.citylim ),
|--10--------20--------30--------40--------50--------60--------70--------80--------9|
|-------- XML error:　The previous line is longer than the max of 90 characters ---------|
　　　　　　　　 informix.st_envelope(<owner>.tsunami.inundation ))
NESTED LOOP JOIN
UDRs in query:
--------------
　　UDR id　:　 662
　　UDR name:　 st_intersects
　　UDR id　:　 626
　　UDR name:　 st_envelope
　　UDR id　:　 567
　　UDR name:　 st_centroid

　　创建函数索引计算城市中心。

　 dbaccess spatialdb -
　 create index citycenterindex
　 on cities( st_centroid( citylim ) st_geometry_ops )
　 using rtree;

　　有关这个索引，需注意以下几点：

　　指定 R-Tree 访问方法。

　　st_centroid() 计算城市中心。

　　st_geometry_ops 指定操作符类。

　　将操作符类与访问方法结合使用，可以指定创建索引和优化查询时使用的操作符。操作符类通常为一组函数。对于 Spatial DataBlade，st_geometry_ops 类由以下函数组成：

ST_Contains()
ST_Crosses()
ST_Equals()
SE_EnvelopesIntersect()
ST_Intersects()
SE_Nearest()
SE_NearestBbox()
ST_Overlaps()
ST_Touches()
ST_Within()

　　执行相同的查询。

dbaccess spatialdb -
select city_name from cities, tsunami
where st_intersects( st_centroid( citylim ), st_envelope( inundation ) );

　　注意：当索引表达式被用于关系表达式，或作为某个操作符类函数的参数时，查询优化器将考虑使用函数索引。由于 st_centroid() 是索引 st_geometry_ops 中指定的操作符类的成员，优化器在生成查询计划时将考虑使用索引。

　　注意：优化器不会对下面这个查询使用函数索引。这是因为 st_distance() 不属于 st_geometry_ops() 操作符类。

select city_name from cities, tsunami
where st_distance( st_centroid( citylim ), inundation ) < 5280;

　　检查 SQL EXPLAIN 输出。该查询计划显示索引扫描使用了函数索引。

　　注意：查询优化器只有在表统计信息保持最新的情况下使用 R-Tree 索引。这意味着您应该在创建索引之前加载数据，或者在加载数据之后更新表的统计信息。

QUERY:
------
select city_name from cities, tsunami
where st_intersects( st_centroid( citylim ), st_envelope( inundation ) )
Estimated Cost: 12920
Estimated # of Rows Returned: 7018
　1) <owner>.tsunami: SEQUENTIAL SCAN
　2) <owner>.cities: INDEX PATH　　<== Index Scan
　　(1) VII Index Keys: informix.st_centroid(citylim)　 (Serial, fragments: ALL)
　　　　VII Index Filter:
　　　　　　informix.st_intersects(informix.st_centroid(<owner>.cities.citylim),
　　　　　　informix.st_envelope(<owner>.tsunami.inundation ))
NESTED LOOP JOIN
UDRs in query:
--------------
　　UDR id　:　 662
　　UDR name:　 st_intersects
　　UDR id　:　 626
　　UDR name:　 st_envelope
　　UDR id　:　 567
　　UDR name:　 st_centroid
　　UDR id　:　 567
　　UDR name:　 st_centroid

　　结束语

　　函数索引是一种强大的性能调优工具。通过减少执行查询时需要检索的元组（tuple）的数量，以及减少查询所需的计算，函数索引能够显著改善性能。

　　除大型对象外，函数索引可以返回任何标准 SQL 类型。同样，函数索引还可以返回用户定义的类型。函数索引可以指定各种不同的访问方法，包括 B-Tree、R-Tree 和用户定义的访问方法。

　　函数索引的存储和维护成本较高。您应该对系统进行成本收益分析，并只在能保证获益的情况下创建函数索引。如果数据大小较小，或者索引对于数据库查询的性能改善帮助不大，则不适合使用函数索引。

　　本文提供了一些有关函数索引的示例。希望我们介绍的这些创建和使用函数索引的方法能够帮助您改善查询搜索的性能。