oracle 如何为“%abc%”搜索创建文本索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7749407/
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
How to create text index for '%abc%' search?
提问by Clinton
I'd like to index queries like x like '%abc%'
我想索引查询，如 x like '%abc%'
If I have a table like the following
如果我有一张像下面这样的表
create table t
(
data varchar(100)
);
I want to create an index to be able to do the following efficiently:
我想创建一个索引以便能够有效地执行以下操作：
select * from t where contains('%abc%');
And this:
和这个：
select * from t where contains('abc%');
I also want this table to be updated live.
我还希望实时更新此表。
How do I create such an index? (I have a feeling I need a ctxcatindex, but I'm confused about what options I need to give it)
如何创建这样的索引？（我感觉我需要一个ctxcat索引，但我对需要给它什么选项感到困惑）
I'm using Oracle 10g.
我正在使用 Oracle 10g。
回答by Kevin Burton
I would use this (set you min and max length to appropiate values)
我会用这个（设置你的最小和最大长度来适当的值）
BEGIN
ctx_ddl.create_preference ('FT_WL', 'BASIC_WORDLIST');
ctx_ddl.set_attribute ('FT_WL', 'substring_index', 'YES');
ctx_ddl.set_attribute ('FT_WL', 'prefix_index', 'YES');
ctx_ddl.set_attribute ('FT_WL', 'prefix_min_length', 1);
ctx_ddl.set_attribute ('FT_WL', 'prefix_max_length', 6);
END;
CREATE INDEX fulltext_idx ON tmp_fulltext (fulltext)
INDEXTYPE IS CTXSYS.CTXCAT
PARAMETERS ('WORDLIST FT_WL')
The parameters are explained here Oracle Text Reference
参数解释在这里Oracle Text Reference
and see this question on how to manage the refresh and how the index may not be quicker than a full scan with high cardinality data:
并查看有关如何管理刷新以及索引可能不会比具有高基数数据的完整扫描更快的问题：
回答by Sushant Butta
Yes, you need to create an environment before you can create domain indexes. You need to have ctxsysuser and necessary ctxappprivileges to create it. Follow the steps explained in this link to have one for your environment. This user is not created by default while installing Oracle.
是的，您需要先创建环境，然后才能创建域索引。您需要拥有ctxsys用户和必要的ctxapp权限才能创建它。按照此链接中说明的步骤为您的环境准备一个步骤。安装 Oracle 时，默认情况下不会创建此用户。
Once you have the all the grants and packages you can create preferences and index as shown.
一旦你拥有所有的赠款和包，你就可以创建首选项和索引，如图所示。
SQL> begin
2 ctx_ddl.create_preference('SUBSTRING_PREF', 'BASIC_WORDLIST');
3 ctx_ddl.set_attribute('SUBSTRING_PREF', 'SUBSTRING_INDEX','TRUE');
4 end;
5 /
Now create a domain index as shown.
现在创建一个域索引，如图所示。
SQL> create index test_idx on test(object_name)
2 indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF MEMORY 50M');
Index created.
select * from test where contains( object_name,'%EXEC%') > 0;
See the link below which explains this with the execution plan. Update 2018:The original link is dead and not backed up on archive.org, unfortunately.
请参阅下面的链接，该链接通过执行计划对此进行了解释。2018 年更新：不幸的是，原始链接已失效且未在 archive.org 上备份。
http://www.oraclebin.com/2012/12/oracle-text-and-domain-indexes.html
http://www.oraclebin.com/2012/12/oracle-text-and-domain-indexes.html
回答by Wazy
Looking at your problem if your database is big then you can use Sphinx Search
查看您的问题，如果您的数据库很大，那么您可以使用Sphinx Search
Sphinx is an open source full text search server, designed from the ground up with performance, relevance (aka search quality), and integration simplicity in mind. It's written in C++ and works on Linux (RedHat, Ubuntu, etc), Windows, MacOS, Solaris, FreeBSD, and a few other systems
Sphinx 是一个开源全文搜索服务器，从头开始设计时就考虑到了性能、相关性（也就是搜索质量）和集成的简单性。它是用 C++ 编写的，适用于 Linux（RedHat、Ubuntu 等）、Windows、MacOS、Solaris、FreeBSD 和一些其他系统
回答by Yahia
You can do that in Oracle only if you have intermedia/Oracle Text option on the server...
只有在服务器上有 intermedia/Oracle Text 选项时，您才能在 Oracle 中执行此操作...
For your example you could use
对于您的示例，您可以使用
create index t_index_data on t(data)
indextype is ctxsys.context
parameters ('DATASTORE CTXSYS.DEFAULT_DATASTORE');
I am not sure if you need to change the type from varchar2(100)to clob.
我不确定您是否需要将类型从 更改varchar2(100)为clob。
For details and options/example regarding this sort of indexes see http://download.oracle.com/docs/cd/A91202_01/901_doc/text.901/a90122/ind4.htm
有关此类索引的详细信息和选项/示例，请参阅http://download.oracle.com/docs/cd/A91202_01/901_doc/text.901/a90122/ind4.htm
