程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> 用DB2 pureXML執行不區分大小寫的高效搜索

用DB2 pureXML執行不區分大小寫的高效搜索

編輯:DB2教程

根據定義,XML 元素和屬性的值是區分大小寫的。例如,如果搜索值為 “Paris” 的 元素,那麼不會找到 “PARIS” 或 “paris”。可以使用 fn:upper-case() 等 XQuery 函數解決這個問題,但是使用這些函數時不能使用 XML 索引,所以性能可能不好。本文解釋如何使用 DB2® pureXML™ 創建不區分大小寫的數據庫以及讓 XML 查詢和 XML 索引發揮預期的作用。用大寫和小寫函數執行不區分大小寫的搜索

下面的示例幫助您更清楚地理解不區分大小寫的搜索。清單 1 定義一個包含一個 INTEGER 列和一個 XML 列的表,並在表中插入 7 行。每行包含一個小的客戶文檔,其中包含 XML 元素

此元素中的值在大小寫方面並不一致。一些值是全大寫的,一些是全小寫的,其他是大小寫混合的(首字母大寫)。如果數據來自不同的應用程序,而這些應用程序采用不同的大小寫數據輸入規則,就會出現這種情況。

清單 1. 示例表和數據

CREATE TABLE customer (id INTEGER, xmldoc XML);

INSERT INTO customer (id, XMLdoc)
VALUES (1,'<Customer id="1"><city>PARIS</city></Customer>'),
    (2,'<Customer id="2"><city>Tokyo</city></Customer>'),
    (3,'<Customer id="3"><city>tokyo</city></Customer>'),
    (4,'<Customer id="4"><city>PARIS</city></Customer>'),
    (5,'<Customer id="5"><city>paris</city></Customer>'),
    (6,'<Customer id="6"><city>Delhi</city></Customer>'),
    (7,'<Customer id="7"><city>Paris</city></Customer>');

如果一個應用程序查詢這些 XML 文檔,尋找某一城市的客戶,那麼很可能需要不區分大小寫的搜索。例如,可能希望找到 Paris 的所有客戶,也就是希望獲取第 1、4、5 和 7 行。但是,如果搜索值 “Paris”,那麼只會返回第 7 行。要想獲取所需的所有四行,可以使用 XQuery 函數 fn:upper-case() 把 city 元素值轉換為大寫並與 “PARIS” 做比較。清單 2 中的查詢就采用這種方式,它會返回 Paris 的所有四個客戶。

清單 2. 選擇 Paris 的客戶

SELECT id, XMLCAST( XMLQUERY('$XMLDOC/Customer/city') AS VARCHAR(15)) AS city
FROM customer
WHERE XMLEXISTS('$XMLDOC/Customer[fn:upper-case(city) = "PARIS"]'); 

如果查詢通過一個參數標志提供搜索值,那麼這個參數也應該轉換為大寫,見 清單 3。這個參數標志(“?”)的類型為 VARCHAR(15) 並作為變量 “c” 傳遞給 XQuery 謂詞。

清單 3. 使用參數標志選擇客戶

SELECT id, XMLCAST( XMLQUERY('$XMLDOC/Customer/city') AS VARCHAR(15)) AS city
FROM customer
WHERE XMLEXISTS('$XMLDOC/Customer[fn:upper-case(city) = fn:upper-case($c)]'
PASSING CAST(? AS VARCHAR(15)) AS "c"); 

圖 1 顯示以上示例查詢的輸出。

用DB2 pureXML執行不區分大小寫的高效搜索

圖 1. 示例查詢的結果

如果只查詢少量數據,或者查詢還包含其他選擇性謂詞,使得大寫謂詞只應用於很小的中間結果集,那麼這種方式效果還不錯。問題是如果使用包含 fn:upper-case() 函數的謂詞,就不會使用 DB2 中的 XML 索引。因此,這種方式不適用於大量數據。

要想避免使用 fn:upper-case() 函數並利用 XML 索引加快查詢,就需要創建不區分大小寫的數據庫。

創建不區分大小寫的 DB2 數據庫

DB2 從 Version 9.5 Fixpack 1 開始支持感知地區的 Unicode 排序規則。這使我們能夠忽略大小寫和/或重音符號。要想創建對於所有字符串比較不區分大小寫的數據庫,需要使用排序規則 UCA500R1,見 清單 4。

清單 4. 創建不區分大小寫的數據庫

CREATE DATABASE testdb
USING CODESET UTF-8 TERRITORY US
COLLATE USING UCA500R1_LEN_S2; 

字符串 UCA500R1_LEN_S2 究竟意味著什麼?UCA500R1 指定此數據庫使用基於 Unicode 5.0.0 標准的默認 Unicode Collation Algorithm(UCA)。因為默認的 UCA 不能同時覆蓋 Unicode 支持的每種語言的排序規則序列,所以可以使用可選屬性定制字符的次序。屬性以下劃線(_)分隔。UCA500R1 關鍵字加上所有屬性構成一個 UCA 排序規則名。

清單 4 中使用的排序規則名包含兩個屬性:LEN 和 S2。LEN 是 L(語言)和 EN(英語的 ISO 639-1 語言編碼)的組合。第二個屬性 S2 指定強度級別,這決定在字符串排序或比較時是否考慮大小寫或重音符號。在 清單 4 中使用強度級別 2,所以 “PARIS” 和 “paris” 是相等的。下面是其他有效值的示例:

UCA500R1_LEN_S1 導致 "cliche" = "Cliche" = "cliché"

UCA500R1_LEN_S2 導致 "cliche" = "Cliche" < "cliché"

UCA500R1_LEN_S3 導致 "cliche" < "Cliche" < "cliché"

在 DB2 Information Center 中可以找到可以作為 UCA 排序規則名的所有組合(參見 參考資料)。

在不區分大小寫的數據庫中查詢 XML 數據

因為此數據庫使用排序規則名 UCA500R1 和強度級別 2,所以現在可以簡化前面的查詢,去掉 fn:upper-case() 函數(清單 5),就像所有數據都是大寫的一樣。無論搜索字符串是 “Paris” 或 “PARIS” 還是其他任何大小寫組合,結果都是相同的。

清單 5. 選擇 Paris 的客戶

SELECT id, XMLCAST( XMLQUERY('$XMLDOC/Customer/city') AS VARCHAR(15)) AS city
FROM customer
WHERE XMLEXISTS('$XMLDOC/Customer[city = "PARIS"]'); 

圖 2. 示例查詢的結果

用DB2 pureXML執行不區分大小寫的高效搜索 

如果通過添加 ORDER BY 子句按提取的 city 值排序,那麼結果集仍然是相同的:PARIS、paris 和 Paris 被當作相同的值。

為了高效地查詢此數據,尤其是在表中行數很大的情況下,應該用 XPath /Customer/city 創建一個 XML 索引,見 清單 6:

清單 6. 創建 XML 索引

   CREATE INDEX customer_lang_idx ON test (XMLdoc)
GENERATE KEY USING XMLPATTERN '/Customer/city' AS SQL VARCHAR(15);

現在,如果用 Visual Explain 或 db2exfmt 解釋此查詢,就會看到這個不區分大小寫的搜索使用了索引:

用DB2 pureXML執行不區分大小寫的高效搜索

圖 3. 在不區分大小寫的數據庫中查詢 Paris 的所有客戶的 Explain Plan

本節介紹的方法有一個潛在的缺點:整個數據庫中所有表中的所有列中的所有數據都是不區分大小寫的。不可能以區分大小寫的方式處理特定的表或列。要麼都區分大小寫,要麼都不區分。

注意,不區分大小寫只應用於元素和屬性值,而不應用於標記名本身。XML 標記和路徑表達式仍然是區分大小寫的。例如,XPath 表達式 /Customer/city(小寫 “c”)和 /Customer/City(大寫 “C”)是不同的。後者不匹配示例數據中的任何元素,因為示例數據中的 元素名是小寫的。

性能

在數據庫中使用定制的排序規則可能影響查詢性能,因為在選擇更寬松的 UCA 設置時,匹配的字符串數量可能會增加。換句話說,在不區分大小寫的數據庫中,字符串比較的開銷可能會略微增加。為了查明區分大小寫的和不區分大小寫的數據庫之間的性能差異,我們創建了一個常規數據庫(區分大小寫)和一個不區分大小寫的數據庫。然後,插入來自 TPoX 基准測試的 20,000 個 CustAcc 文檔並在這兩個數據庫中對各種查詢進行測試。

對於只涉及少量到中等數量的行的查詢,兩個測試數據庫之間的性能差異可以忽略不計。我們發現涉及大量行的查詢的性能差異比較大,比如對所有 20,000 個 XML 文檔進行全表掃描並對每個文檔比較字符串。在不區分大小寫的數據庫中,這種查詢花費的時間增加了 5% 到 8%。因此,實現不區分大小寫的搜索需要付出的代價並不大。

結束語

以不區分大小寫的方式搜索 DB 2 數據有多種方法,比如使用生成的列(請參見 參考資料)。盡管這些方法都適合關系數據,但是不適合查詢 XML 數據。以不區分大小寫的方式處理 XML 數據的最佳方法是用定制的 Unicode 排序規則創建數據庫。這使數據庫中的所有字符串值比較都采用不區分大小寫的方式,避免妨礙使用 XML 索引和關系索引。由於不區分大小寫或重音符號,會增加匹配的字符串,但是增加的開銷非常低。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved