永城信息网
美食
当前位置:首页 > 美食

DB2用户定义函数实现多种语言的排序

发布时间:2019-09-13 19:58:37 编辑:笔名

准备要使用的排序规则是一种开销很大的操作。因此,在查询执行时不要改变排序规则名。例如,考虑以下表和查询:

NAMES: NAMELANGUAGE ------------ BobLEN MarcLFR Serge LDE SELECT NAME FROM NAMES WHERE ICU.SORTKEY(NAME, LANGUAGE) = ICU.SORTKEY(:hv, LANGUAGE)

在这个例子中,对于每一行都要准备一个新的排序规则。这样做的效率非常低。如果 SORTKEY 的第二个参数替换为一个字面字符串或主机变量,那么查询的性能会好得多。

注意,可以在一个查询中混合使用不同的排序规则,只要每个排序规则在不同的 SORTKEY 实例中。以下查询的性能会好得多:

SELECT NAME FROM NAMES WHERE ICU.SORTKEY(NAME, 'LFR') = ICU.SORTKEY(:hv, 'LFR')

尽可能少使用 SORTKEY

如果知道数据是一致的,那么就不需要对每个操作都使用 SORTKEY。例如,考虑前面的 查询 3 和 查询 4。如果数据是以一致的方式输入的,比如总是使用 ä、ö 和 ü,或者已经对数据进行了清理,将所有 ae、oe 和 ue 替换为 ä、ö 和 ü,那么查询 3 和查询 4 会返回同样的结果,而查询 3 运行得快的多。

如果数据是一致的,就不经常需要 SORTKEY。尽可能使用标准的 SQL 比较操作符,并在最后的 ORDER BY 中使用 SORTKEY。

使用生成的列

如果数据库常常使用很少几个排序规则,那么可以考虑使用生成的列预先计算 SORTKEY 的结果,并将这些结果存储在数据库中。

例如,假设一个数据库通常只需要法语和德语排序规则。在这种情况下,根据表的总规模,可以考虑创建生成的列来保存 SORTKEY 的结果。例如:

清单 3. 创建生成的列来保存 SORTKEY 的结果

CREATE TABLE NAMES ( NAME VARCHAR(50), NAME_FR_KEY VARCHAR(1200) GENERATED ALWAYS AS (ICU.SORTKEY(NAME, 'LFR')), NAME_DE_KEY VARCHAR(1200) GENERATED ALWAYS AS (ICU.SORTKEY(NAME, 'LDE')) ) SELECT NAME FROM NAMES ORDER BY ICU.SORTKEY(NAME, 'LFR')

当 DB2 查询编译器对这个查询进行运算时,它会意识到 ICU.SORTKEY(NAME, 'LFR') 的值已经计算出来了,它会使用 NAME_FR_KEY 列来替代这个值。但是,如果查询使用 ICU.SORTKEY(NAME, 'LES') (西班牙语排序规则),那么 SORTKEY 函数必须作为查询的一部分执行。

不幸的是,将生成的列记录为 VARCHAR(1200) 值会占用表中的大量空间。好在,还有一些办法。

一个办法是修改 createfn.db2,让 SORTKEY 产生长度更短的结果类型。如果这样做了,那么应该减小 sortkey.c 中的常量 MAX_RESULT,还应该重新编译这个 UDF。

另一个办法是将 SORTKEY 的结果转换为更短的 VARCHAR 值。但是,对于使用生成的列的优化器,必须在每个引用中使用同样的转换。这种办法如下所示:

清单 4. 在每个引用中使用同样的转换

CREATE TABLE NAMES ( NAME VARCHAR(50), NAME_FR_KEY VARCHAR(600)GENERATED ALWAYS AS (CAST(ICU.SORTKEY(NAME, 'LFR') AS VARCHAR(600))), NAME_DE_KEY VARCHAR(600)GENERATED ALWAYS AS (CAST(ICU.SORTKEY(NAME, 'LDE') AS VARCHAR(600))) ) SELECT NAME FROM NAMES ORDER BY CAST(ICU.SORTKEY(NAME, 'LFR') AS VARCHAR(600))

总是需要指定转换,这使这种办法不够理想。可以使用下面的源函数将转换隐藏起来:

清单 5. 使用源函数将转换隐藏起来

CREATE FUNCTION MY_SORTKEY(VARCHAR(50), VARCHAR(50)) RETURNS VARCHAR(600) FOR BIT DATASOURCE ICU.SORTKEYCREATE TABLE NAMES ( NAME VARCHAR(50), NAME_FR_KEY VARCHAR(600) GENERATED ALWAYS AS (MY_SORTKEY(NAME, 'LFR')), NAME_DE_KEY VARCHAR(600) GENERATED ALWAYS AS (MY_SORTKEY(NAME, 'LDE')) ) SELECT NAME FROM NAMES ORDER BY MY_SORTKEY(NAME, 'LFR')

不管使用哪种方法,重要的考虑因素都是生成的列的长度。SORTKEY 结果的长度可能比原来的字符串长。简单的规则是,对于输入字符串中的每个字符,在输出字符串中允许有 12 字节。(对于某些不常见的排序规则和输入值组合,这个空间甚至也可能不够。)但是,许多排序规则会产生比这短得多的排序键,因此在决定生成的列的大小时,对要使用的排序规则和数据进行一些实验是有帮助的。

热淋清颗粒的用量
小孩经常发烧
小孩不爱吃饭是什么原因
丁桂薏芽健脾凝胶怎么吃