数据库不区分大小写索引? [英] Database Case Insensitive Index?

问题描述

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

I have a query where I am searching against a string:

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

现在，这可以正常工作，但不能很好地扩展，我需要对其进行优化.我已经找到了一个选项生成的视图，或类似的东西，但我希望使用索引的更简单的解决方案.

Now, this works fine, but it doesn't scale well, and I need to optimize it. I have found an option along the lines of creating a generated view, or something like that, but I was hoping for a simpler solution using an index.

我们正在使用 DB2，我真的很想使用 索引中的表达式，但这个选项似乎只在 z/OS 上可用，但是我们正在运行 Linux.我还是尝试了表达式索引:

We are using DB2, and I really want to use an expression in an index, but this option seems to only be available on z/OS, however we are running Linux. I tried the expression index anyways:

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

当然，它会在 UPPER(name) 上窒息.

But of course, it chokes on the UPPER(name).

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

Is there another way I can create an index or something similar in this manner such that I don't have to restructure my existing queries to use a new generated view, or alter my existing columns, or any other such intrusive change?

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

I'm open to hearing solutions for other databases... it might carry over to DB2...

推荐答案

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

You could add an indexed column holding a numerical hash key of the city name. (With duplicates allowed).

然后你可以做一个多子句 where :

Then you could do a multi-clause where :

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

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

或者，查看您的数据库手册并查看用于创建表索引的选项.可能会有帮助.

Alternatively, go through your db manual and look at the options for creating table indexes. There might be something helpful.

这篇关于数据库不区分大小写索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！