我有一个查询，我正在搜索一个字符串：

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 ...

解决方案

回答

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

然后，我们可以执行多个条款：

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

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

另外，请查阅数据库手册，并查看用于创建表索引的选项。可能有帮助。

回答

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

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

回答

PostgreSQL还支持索引函数的结果：

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

我唯一想到的另一种选择是通过创建另一列来保存大写版本(由触发器更新)并为该索引建立索引，从而稍微规范化数据。死神！

回答

我不知道这在DB2中是否可行，但是我将告诉我们如何在SQL Server中执行此操作。我认为MSSQL做到这一点的方式是ANSI标准，尽管特定的排序规则字符串可能有所不同。无论如何，如果可以做到这一点而又不浪费应用程序的其余部分，那么"名称"列是否需要区分大小写？ -尝试通过更改排序规则使整个列不区分大小写，然后对列进行索引。

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

...其中" nvarchar(200)"代表我们当前的列数据类型是什么。排序规则字符串的" CI"部分将其标记为在MSSQL中不区分大小写。

解释一下...我的理解是索引将按照索引列的排序规则的顺序存储值。使列的排序规则不区分大小写，将使索引存储区" San Francisco"，" SAN FRANCISCO"和" san francisco"一起使用。然后，我们只需要从查询中除去" UPPER()"，DB2应该知道它可以使用索引。

同样，这完全基于我对SQL Server的了解，加上几分钟的时间来了解SQL-92规范。它可能适用于DB2，也可能不适用于DB2.

回答

DB2在整理方面不强。而且它没有基于函数的索引。

如果我们可以接受必须在应用程序中进行哈希处理(据我所知，因为DB2不具有SHA或者MD5函数)，Niek Sanders的建议将行得通。

但是，如果我们是我，那么我将使用CREATE TABLE AS创建一个物化视图(MQT ==物化查询表，以db2术语表示)，并添加一个带有名称的预计算大写变体的列。注：我们可以将索引添加到DB2中的实例化视图。

回答

简短的回答，不。

长答案，是的，如果我们正在大型机上运行，​​但不是，则必须使用其他技巧。

现在，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对象。

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