在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 文件中。下表展示了該文件的位置和名稱。
平台 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
示例：使用 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 和用戶定義的訪問方法。
函數索引的存儲和維護成本較高。您應該對系統進行成本收益分析，並只在能保證獲益的情況下創建函數索引。如果數據大小較小，或者索引對於數據庫查詢的性能改善幫助不大，則不適合使用函數索引。
本文提供了一些有關函數索引的示例。希望我們介紹的這些創建和使用函數索引的方法能夠幫助您改善查詢搜索的性能。
