oracle 数据库不区分大小写索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12855/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Database Case Insensitive Index?
提问by Mike Stone
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 optionalong 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:
我们正在使用 DB2，我真的想在索引中使用一个表达式，但是这个选项似乎只在 z/OS 上可用，但是我们运行的是 Linux。无论如何，我尝试了表达式索引：
CREATE INDEX city_upper_name_idx
ON city UPPER(name) ALLOW REVERSE SCANS;
But of course, it chokes on the UPPER(name).
但当然，它会在 UPPER（名称）上窒息。
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...
编辑：我愿意听取其他数据库的解决方案......它可能会延续到 DB2......
回答by nsanders
You could add an indexed column holding a numerical hash key of the city name. (With duplicates allowed).
您可以添加一个索引列，其中包含城市名称的数字哈希键。（允许重复）。
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.
或者，浏览您的数据库手册并查看用于创建表索引的选项。可能有一些有用的东西。
回答by paxdiablo
Short answer, no.
简短的回答，没有。
Long answer, yes if you're running on the mainframe, but you're not, so you have to use other trickery.
答案很长，是的，如果你在大型机上运行，但你不是，所以你必须使用其他技巧。
DB2 (as of DB2/LUW v8) now has generated columns so you can:
DB2（从 DB2/LUW v8 开始）现在已经生成了列，因此您可以：
CREATE TABLE tbl (
lname VARCHAR(20),
fname VARCHAR(20),
ulname VARCHAR(20) GENERATED ALWAYS AS UPPER(lname)
);
and then create an index on ulname. I'm not sure you're going to get it simpler than that.
然后在 ulname 上创建一个索引。我不确定你会不会比这更简单。
Before that, you used to have to use a combination of insert and update triggers to ensure the ulname column was kept in sync, and this was a nightmare to maintain. Also, now that this functionality is part of the core DBMS, it's been highly optimized (it's much faster than the trigger-based solution) and doesn't get in the way of real user triggers, so no extra DB objects to maintain.
在此之前，您曾经必须结合使用插入和更新触发器来确保 ulname 列保持同步，这是维护的噩梦。此外，现在这个功能是核心 DBMS 的一部分，它已经过高度优化（它比基于触发器的解决方案快得多）并且不会妨碍真正的用户触发器，因此不需要维护额外的 DB 对象。
See herefor details.
有关详细信息，请参见此处。
回答by Mark Harrison
Oracle supports function-based indexes. Their canonical example:
Oracle 支持基于函数的索引。他们的规范示例：
create index emp_upper_idx on emp(upper(ename));
回答by Neall
PostgreSQL also supports indexing the results of a function:
PostgreSQL 还支持索引函数的结果：
CREATE INDEX mytable_lower_col1_idx ON mytable (lower(col1));
The only other option I can think of is to de-normalize your data a bit by creating another column to hold the upper-case version (updated by triggers) and index that. Blech!
我能想到的唯一其他选择是通过创建另一列来保存大写版本（由触发器更新）并索引它来稍微对数据进行非规范化。布莱克！
回答by Kevin Crumley
I don't know whether this would work in DB2, but I'll tell you how I'd do this in SQL Server. I thinkthe way MSSQL does this is ANSI standard, though the specific collation strings may differ. Anyway, if you can do this without trashing the rest of your application -- are there other places where the "name" column needs to be case-sensitive? -- try making that whole column case-insensitive by changing the collation, then index the column.
我不知道这是否适用于 DB2，但我会告诉您我将如何在 SQL Server 中做到这一点。我认为MSSQL 执行此操作的方式是 ANSI 标准，尽管特定的排序规则字符串可能有所不同。无论如何，如果您可以在不破坏应用程序其余部分的情况下执行此操作 - 是否还有其他地方需要“名称”列区分大小写？-- 尝试通过更改排序规则使整个列不区分大小写，然后索引该列。
ALTER TABLE city ALTER COLUMN name nvarchar(200)
COLLATE SQL_Latin1_General_CP1_CI_AS
...where "nvarchar(200)" stands in for whatever's your current column data type. The "CI" part of the collation string is what marks it as case-insensitive in MSSQL.
...其中“nvarchar(200)”代表您当前的列数据类型。归类字符串的“CI”部分在 MSSQL 中将其标记为不区分大小写。
To explain... my understanding is that the index will store values in the order of the indexed column's collation. Making the column's collation be case-insensitive would make the index store 'San Francisco', 'SAN FRANCISCO', and 'san francisco' all together. Then you should just have to remove the "UPPER()" from your query, and DB2 should know that it can use your index.
解释一下……我的理解是索引将按照索引列的排序规则存储值。使列的排序规则不区分大小写将使索引存储 'San Francisco'、'SAN FRANCISCO' 和 'san francisco' 放在一起。然后您应该只需要从您的查询中删除“UPPER()”，并且 DB2 应该知道它可以使用您的索引。
Again, this is based solely on what I know about SQL Server, plus a couple minutes looking at the SQL-92 spec; it may or may not work for DB2.
同样，这完全基于我对 SQL Server 的了解，再加上几分钟查看 SQL-92 规范；它可能适用于 DB2，也可能不适用于 DB2。
回答by Troels Arvin
DB2 isn't strong regarding collation. And it doesn't have function-based indexes.
DB2 在整理方面并不强。它没有基于函数的索引。
Niek Sanders's suggestion would work, if you can accept that the hashing has to happen in your application (as DB2 doesn't have SHA or MD5 functions, as far as I know).
如果您可以接受散列必须在您的应用程序中发生（因为据我所知，DB2 没有 SHA 或 MD5 函数），Niek Sanders 的建议会起作用。
However, if I were you, I'd create a materialized view (MQT == Materialized Query Table, in db2 parlance) using CREATE TABLE AS, adding a column with a pre-computed upper-case variant of the name. Note: You may add indexes to materialized views in DB2.
但是，如果我是您，我会使用CREATE TABLE AS创建一个物化视图（MQT == Materialized Query Table，在 db2 中），添加一个带有名称的预先计算的大写变体的列。注意：您可以向 DB2 中的物化视图添加索引。
