数据库不敏感指数？ [英] Database Case Insensitive Index?

问题描述

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

  SELECT county FROM city WHERE UPPER（name）='旧金山'; 
  

现在，这工作正常，但是它不能很好地扩展，我需要优化它。我有找到一个选项沿着创建一个生成的视图，或类似的东西，但我希望使用索引的一个更简单的解决方案。

我们正在使用DB2，我真的想使用一个在索引中href =http://ibmsystemsmag.blogs.com/db2utor/2007/11/db2-9-index-wit.html =nofollow noreferrer>表达式，但是这个选项似乎只在z / OS上可用，但是我们正在运行Linux。我尝试了表达式索引：

  CREATE INDEX city_upper_name_idx 
 ON city UPPER（name）ALLOW REVERSE SCANS; 
  

但当然，它阻碍了UPPER（名称）。

有没有其他方式可以用这种方式创建一个索引或类似的东西，这样我就不用重组现有的查询来使用新的生成的视图，或者改变我现有的列这样的侵入性变化？

编辑：我可以听取其他数据库的解决方案...它可能会转移到DB2 ...

解决方案

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

然后，您可以执行多个条件，其中：

hash = [计算哈希键对于'旧金山']

SELECT county FROM city WHERE cityHash = hash AND UPPER（name）='SAN FRANCISCO';

或者，通过您的数据库手册，查看创建表索引的选项。可能有帮助。

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.

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;

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?

EDIT: 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).

Then you could do a multiclause 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屋！