Oracle 中的子字符串索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8487520/
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
Substring-indexing in Oracle
提问by usr-local-ΕΨΗΕΛΩΝ
I just found that our current database design is a little inefficient according to the SELECTqueries we perform the most. IBANs are positional coordinates, according to nation-specific formats.
我刚刚发现，根据SELECT我们执行最多的查询，我们当前的数据库设计有点低效。根据国家特定的格式，IBAN 是位置坐标。
Since we mostly perform JOINs and WHEREs on a precise substring of IBAN columns in some tables, my question is about assigning an index to the substring(s) of a column
由于我们主要对某些表中的 IBAN 列的精确子字符串执行JOINs 和WHEREs，因此我的问题是为列的子字符串分配索引
Are we forced to add redundant and indexed columns to the table? Ie. add columns NATION_CODE, IBAN_CIN, IT_CIN, IT_ABI, IT_CAB, IT_ACCOUNT(where the IT_ fields are considered only for accounts starting in ITXX) each one with appropriate secondary indexing oris there any special kind of secondary index that can be applied only on a substring of a column?
我们是否被迫向表中添加冗余和索引列？IE。添加列NATION_CODE, IBAN_CIN, IT_CIN, IT_ABI, IT_CAB, IT_ACCOUNT（其中 IT_ 字段仅适用于以 ITXX 开头的帐户），每个字段都具有适当的二级索引，或者是否有任何特殊类型的二级索引只能应用于列的子字符串？
The first solution could make the DB more complex since IBAN accounts are used all along the DBMS (and, obviously, I have no full control over design).
第一个解决方案可能会使 DB 变得更加复杂，因为在 DBMS 中一直使用 IBAN 帐户（显然，我无法完全控制设计）。
Thank you
谢谢
[Edit] Typical query
[编辑] 典型查询
SELECT * FROM DELEGATIONS WHERE SUBSTR(IBAN, 6, 5) IN (SELECT ABI FROM BANKS WHERE ANY_CONDITION)
Extracts all payment delegations where the target account belongs to any of the banks that match CONDITION. Should be changed to
提取目标账户属于匹配 CONDITION 的任何银行的所有付款委托。应该改为
SELECT * FROM DELEGATIONS WHERE SUBSTR(IBAN, 1, 2) = 'IT' AND SUBSTR(IBAN, 6, 5) IN (SELECT ABI FROM BANKS WHERE ANY_CONDITION)
to make sure that BBAN really holds the bank code in digits [6-11]
确保 BBAN 确实以数字形式保存银行代码 [6-11]
回答by René Nyffenegger
You're looking for a function based index:
您正在寻找基于函数的索引：
create index ix_substring on TABLE (substr(COLUMN, 4, 9))
回答by Mike Meyers
If you're using Oracle 11g, you could look at using virtual columnsand then indexing those. This is pretty much equivalent to René's answer but might provide a bit more formality around the use of the data so that only the correct characters are used.
如果您使用的是 Oracle 11g，则可以考虑使用虚拟列，然后对它们进行索引。这几乎等同于 René 的答案，但可能会在数据的使用方面提供更多形式，以便仅使用正确的字符。
Virtual columns won't take up any additional space in the database, although any index you create on that virtual column will.
虚拟列不会占用数据库中的任何额外空间，尽管您在该虚拟列上创建的任何索引都会占用。
