用DB2 pureXML执行不区分大小写的高效搜索

数据库 数据库运维
本文解释如何使用 DB2® pureXML™ 创建不区分大小写的数据库以及让 XML 查询和 XML 索引发挥预期的作用。

根据定义,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 显示以上示例查询的输出。

图 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. 示例查询的结果

 
 

如果通过添加 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 解释此查询,就会看到这个不区分大小写的搜索使用了索引:

图 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. Oracle数据库中段管理的四个技巧
  2. 最简单删除SQL Server中所有数据的方法
  3. DB 2数据库性能优化技巧详解
责任编辑:彭凡 来源: IBM
相关推荐

2011-11-30 21:46:24

ibmdwDB2 pureXML

2010-08-20 12:49:49

DB2 pureXML

2009-01-18 16:33:09

pureXMLDB2 pureXMLXML

2009-03-10 10:31:26

DB2XMLpureXML

2009-09-22 12:25:04

ibmdwDB2

2010-08-09 17:52:38

DB2 pureXML

2009-04-17 10:07:42

2011-08-30 10:10:30

UbuntuLinuxMySQL

2009-11-23 19:57:01

ibmdwDB2

2010-06-04 20:04:10

MySQL数据库大小写

2010-11-03 11:26:39

DB2表空间

2010-11-01 11:45:06

DB2管理页大小

2010-09-07 16:11:19

执行DB2命令

2010-11-01 17:02:52

DB2页大小

2010-11-25 16:09:58

mysql查询大小写

2010-08-19 13:59:01

DB2命令

2011-05-07 10:47:29

Oracle大小写

2021-06-15 09:39:45

Oracle敏感数据库

2010-07-28 10:13:06

DB2查询Table

2018-09-21 09:13:32

Windows 10文件夹属性
点赞
收藏

51CTO技术栈公众号