不区分大小写的数据库索引？

Question

我有一个查询，其中我正在针对一个字符串进行搜索：

SELECT county FROM city WHERE UPPER(name) = 'SAN FRANCISCO';

现在，这个方案运作良好，但它并不具备良好的可扩展性，我需要进行优化。我已经 发现了一个选项，可以创建生成视图或类似的东西，但我希望使用索引来实现更简单的解决方案。

我们正在使用DB2，并且我真的想在索引中使用表达式，但是这个选项似乎只在z/OS上可用，而我们正在运行Linux。无论如何，我尝试使用表达式索引：

CREATE INDEX city_upper_name_idx
ON city UPPER(name) ALLOW REVERSE SCANS;

当然，它会在UPPER(name)处出现错误。

是否有另一种方法可以创建索引或类似的东西，以便我不必重构现有查询以使用新生成的视图，或更改现有列，或进行任何其他这样的侵入性更改？

编辑：我愿意听取其他数据库的解决方案……它可能会延续到DB2……

- Mike Stone

Answer 1

您可以添加一个索引列，其中包含城市名称的数字哈希键（允许重复）。

然后，您可以执行多个子句 where：

hash = [compute hash key for 'SAN FRANCISCO']

SELECT county 
FROM city 
WHERE cityHash = hash 
  AND UPPER(name) = 'SAN FRANCISCO' ;

或者，通过您的数据库手册查看创建表索引的选项。可能会有一些有用的东西。

- Niek Sanders

Answer 2

简短回答，不行。

长话短说，如果你在主机上运行，那么是可以的，但你不是，所以你必须使用其他的技巧。

DB2（截至DB2/LUW v8版本）现在有生成列，因此你可以：

CREATE TABLE tbl (
    lname  VARCHAR(20),
    fname  VARCHAR(20),
    ulname VARCHAR(20) GENERATED ALWAYS AS UPPER(lname)
);

然后在ulname上创建一个索引。我不确定你能否比这更简单。

在此之前，您必须使用插入和更新触发器的组合来确保ulname列保持同步，这是一场噩梦般的维护。而且，现在这个功能已经成为核心DBMS的一部分，它已经被高度优化（比基于触发器的解决方案快得多），并且不会妨碍真正的用户触发器，因此无需维护额外的DB对象。

有关详细信息，请参见此处。

- Pax

Answer 3

我不知道这种方法在DB2中是否可行，但我会告诉你如何在SQL Server中实现。 我认为MSSQL的做法是符合ANSI标准的，尽管具体的排序字符串可能有所不同。 无论如何，如果您可以在不破坏应用程序的其他部分的情况下进行操作 - 是否存在其他需要区分大小写的“名称”列的地方？ - 尝试通过更改排序规则使整个列不区分大小写，然后对该列进行索引。

ALTER TABLE city ALTER COLUMN name nvarchar(200) 
    COLLATE SQL_Latin1_General_CP1_CI_AS

......其中"nvarchar(200)"代表您当前列数据类型的内容。在MSSQL中，“CI”部分是标记其为不区分大小写的排序字符串。

简单解释一下，我的理解是，索引将按照索引列的排序方式存储值。将列的排序方式设置为不区分大小写会使索引将“San Francisco”、“SAN FRANCISCO”和“san francisco”全部存储在一起。然后您只需要从查询中删除“UPPER()”，DB2就应该知道它可以使用您的索引。

再次强调，这仅基于我对SQL Server的了解，再加上查看SQL-92规范的几分钟时间；它可能适用于DB2，也可能不适用。

- kcrumley

Answer 4

Oracle支持基于函数的索引。它们的典型示例：

 create index emp_upper_idx on emp(upper(ename));  

- Mark Harrison

---

不幸的是，DB2/LUW目前还没有这个功能，但很可能会有，因为DB2/z已经具备了该功能。 - paxdiablo

Answer 5

PostgreSQL 还支持对函数结果进行索引：

CREATE INDEX mytable_lower_col1_idx ON mytable (lower(col1));

我能想到的另一个选项是通过创建另一列来稍微非规范化您的数据，以保存大写版本（由触发器更新），并对其进行索引。不太好！

- Neall

---

不需要触发器等，DB2支持生成列。 - paxdiablo

Answer 6

DB2在排序方面并不强大。而且它没有基于函数的索引。

Niek Sanders的建议是可行的，如果您可以接受哈希处理必须在应用程序中进行（据我所知，DB2没有SHA或MD5函数）。

然而，如果我是你，我会使用CREATE TABLE AS创建一个物化视图（MQT == Materialized Query Table，在db2术语中），添加一个预先计算的名称大写变体的列。注意：您可以在DB2中为物化视图添加索引。

- Troels Arvin

---

从存储和速度两方面来看，只需向现有表添加另一个生成的列而不是拥有整个新表会更便宜。我对于DB2在排序方面的弱点表示异议。还有，你姐姐长得...抱歉，我有点激动了 :-）。 - paxdiablo