代码之家 › 专栏 › 技术社区 › Mike Stone

数据库不区分大小写索引?

db2 indexing oracle database sql

Mike Stone · 技术社区 · 16 年前

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

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

现在,这个工作很好,但它不能很好地扩展,我需要优化它。我有 found an option 创建一个生成的视图,或者类似的东西,但是我希望使用索引可以得到一个更简单的解决方案。

我们使用的是DB2,我非常想使用 expression in an index

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

但当然,它扼杀了上层(名字)。

有没有其他方法可以用这种方式创建索引或类似的东西,这样我就不必重新构造现有的查询来使用新生成的视图,或者更改现有的列,或者进行任何其他此类侵入性的更改?

编辑:我愿意听取其他数据库的解决方案。。。它可能会转移到DB2。。。

6 回复 | 直到 8 年前

C B dkretz 7 年前

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

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

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

或者,阅读db手册并查看创建表索引的选项。可能有些有用的东西。

paxdiablo 16 年前

简短的回答,不。

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

然后在ulname上创建索引。我不确定你会不会把事情弄得更简单。

在此之前,您必须使用insert和update触发器的组合来确保ulname列保持同步,这是一个需要维护的噩梦。而且,现在这个功能是核心DBMS的一部分,它已经被高度优化(比基于触发器的解决方案快得多),并且不会妨碍真正的用户触发器,因此没有额外的DB对象需要维护。

看到了吗 here 详情。

ddimitrov 16 年前

认为

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

…其中“nvarchar(200)”代表当前列的数据类型。排序规则字符串的“CI”部分在MSSQL中将其标记为不区分大小写。

为了解释。。。我的理解是索引将按索引列的排序规则存储值。使列的排序不区分大小写会使索引存储“旧金山”、“旧金山”和“旧金山”。然后您只需从查询中删除“UPPER()”,DB2就应该知道它可以使用您的索引。

Mark Harrison 16 年前

Oracle支持基于函数的索引。他们的典范:

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

Neall 16 年前

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

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

Troels Arvin 16 年前

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

但是,如果我是您,我会使用 CREATE TABLE AS ,添加具有预先计算的名称的大写变体的列。注意:您可以在DB2中向物化视图添加索引。