如何更好地使用Oracle全文索引
SELECT * FROM my text WHERE INSTR(the text，' Oracle ')& gt；0;
SELECT * FROM mytext，其中文本类似于“% Oracle %”；
在许多情况下，使用instr和like是理想的，特别是当搜索只跨越一个小表时。但是这些文本定位方式会导致全表扫描，耗费资源，搜索功能非常有限。所以在搜索海量文本数据时，建议使用oralce提供的全文搜索功能，建立全文搜索。步骤1:检查并设置数据库角色。首先，检查数据库中是否有CTXSYS用户和CTXAPP基石。如果没有这个用户和角色，这意味着在创建数据库时没有安装intermedia函数。您必须修改数据库才能安装此功能。默认情况下，ctxsys的用户是锁定的，因此应该首先启用ctxsys的用户。第二步:将ctxsys用户下ctx_ddl的执行权限授予想要使用全文索引的用户，例如:
将ctx_ddl上的execute授予pomoho
步骤3:设置词法分析器(lexer)
Oracle实现全文检索，其机制其实很简单。即通过甲骨文专利的词法分析器(lexer)找出文章中所有的表意单位(甲骨文称之为$ term)，并记录在一组以dr$开头的表格中，同时记录$ term的位置、频率、哈希值等信息。在搜索时，Oracle从这组表中查找相应的$ term，计算其出现频率，并根据一种算法计算每个文档的得分，这就是所谓的‘匹配率’。Lexer是这个机制的核心，决定了全文检索的效率。Oracle为不同的语言提供了不同的lexer，我们通常可以使用其中的三种:
N basic_lexer:针对英语。它可以根据空格和标点符号将英语单词从句子中分离出来，还可以自动将一些出现过于频繁、已经失去检索意义的单词视为‘垃圾’，如if、is等。，处理效率高。然而，这个词用在汉语中存在许多问题。因为它只识别空格和标点符号，而中文句子中通常没有空格，所以它会把整个句子当作一个$ term，事实上它会失去检索能力。以“中国人民站起来了”这句话为例。basic_lexer分析的结果只有一个$ term，就是“中国人民站起来了”。如果此时搜索“中国”，将无法检索到相关内容。
N chinese_vgram_lexer:支持所有中文字符集的专用中文分析器(ZHS 16 GB 231280 zhs 16 gbk zht 32 euchzht 16 big 5 zht 32 triszht 16 ms win 950 zht 16 hkk)。分析器以词为单位分析汉语句子。“中国人民站起来了”这句话，将被它分析为:“中国”、“中国人”、“中国人”、“人民”、“人民站起来”、“起来”、“来了”、“来了”。可以看出，这种分析方法，实现算法非常简单，可以实现‘一网打尽’，但效率并不尽如人意。
N chinese_lexer:这是一个新的中文分析器，只支持utf8字符集。正如我们上面看到的，分析器中文vgram lexer是非常机械的，因为它不知道常用的中文单词。比如上面的‘人站’和‘站起来’在中文里永远不会单独出现，所以这种$ term是没有意义的，会影响效率。chinese_lexer最大的改进是分析器可以理解大多数常用的中文单词，因此可以更高效地分析句子。以上两个傻逼单位不会再出现，大大提高了效率。但它只支持utf8。如果你的数据库是zhs16gbk字符集，那就只能用傻逼的中文vgram lexer了。
如果不做任何设置，Oracle默认使用解析器basic_lexer。要指定使用哪个词法分析器，可以这样做:
首先，在当前用户下创建一个首选项(例如，在pomoho用户下执行下面的语句)。
exec CTX _ DDL . create _ preference(' my _ lexer '，' Chinese _ vgram _ lexer ')；
其次，在建立全文索引时，指定所使用的词法分析器:
在mytable(mycolumn)上创建索引myindex索引类型为ctxsys.context
参数(' lexer my _ lexer ')；
这样建立的全文索引将使用chinese_vgram_lexer作为解析器。
第四步:建立索引。
使用以下语法建立全文索引
创建索引[架构。]架构上的索引。]表(列)索引类型为ctxsys.context [ONLINE]
LOCAL[(PARTITION[PARTITION][PARAMETERS(' paramstring ')]
[，PARTITION[PARTITION][PARAMETERS(' paramstring ')]]]]
[PARAMETERS(paramstring)][PARALLEL n][UNUSABLE]；
示例:
在pubmenu上创建索引CTX _ idx _ menuname(menuname)
indextype是ctxsys.context参数(' lexer my_lexer ')
使用索引
使用全文索引很简单，可以通过以下方式实现:
select * from pub menu where contains(menu，'上传图片')> 0
全文索引的类型
已建立的Oracle Text索引称为域索引，它包括四种索引类型:
l上下文
2 CTXCAT
3 CTXRULE
4 CTXXPATH
根据您的应用程序和文本数据类型，您可以选择任何一种。
为多个字段建立全文索引
很多时候，需要从多个文本字段中查询符合要求的记录。这时候就需要为多个字段建立全文索引。例如，如果需要从pmhsubjects的subjectname和briefintro中进行全文检索，则需要执行以下步骤:
建议首选多字段索引
以ctxsys身份登录并执行:
EXEC CTX _ DDL . create _ preference(' CTX _ idx _ subject _ pref '，
多列数据存储')；
建立与首选项相对应的字段值(以ctxsys身份登录)
EXEC CTX _ DDL . set _ attribute(' CTX _ idx _ subject _ pref '，' columns '，' subjectname，brief intro ')；
建立全文索引
在pmhsubjects(subjectname)上创建索引ctx_idx_subject
INDEXTYPE ISctxsys。上下文参数('数据存储CTX sys . CTX _ idx _ subject _ pref lexer my _ lexer ')
使用索引
select * from pmh subjects where contains(主题名，“李宇春”)> 0
全文索引的维护
为了CTXSYS。上下文索引，在应用程序对基表执行DML操作后，需要维护基表的索引。索引维护包括索引同步和索引优化。
索引建立后，我们可以发现Oracle在这个用户下自动生成了下面的表:(假设索引名为myindex):
$myindex$I博士，$myindex$K博士，$myindex$R博士，$myindex$N博士，其中表I最重要。您可以查看该表，了解它包含的内容:
SELECT token_text，token _ count FROM dr $ I _ rsk 1 $ I其中ROWNUM & lt= 20;
这里就不列举查询了。我拿了。如您所见，保存在这个表中的实际上是Oracle在分析您的文档后生成的$ term记录，包括$ term的位置、频率和哈希值。当文档内容发生变化时，可想而知，这个I表的内容也应该发生相应的变化，以保证Oracle在做全文检索时能够正确地检索到内容(因为所谓的全文检索其实就是查询这个表的核心)。这使用了sync(同步)和optimize(优化)。
Sync:将新的$ term保存到I表中；
优化:清除I表的垃圾，主要是删除I表中已删除的$ term。
当基表中的索引文档被插入、更新或删除时，基表的更改不会立即影响索引，直到索引被同步。您可以查询视图CTX _用户_待定来查看相应的更改。例如:
SELECT pnd_index_name，pnd_rowid，
TO _ CHAR(PND _时间戳，' dd-mon-yyyy hh24:mi:ss ')时间戳
来自ctx _ user _ pending
该语句的输出类似于以下内容:
PND索引名称PND ROWID时间戳
- - -
MYINDEX aaadxnaabaaas 3 saac 06-oct-1999 15:56:50
同步和优化方法:可以使用Oracle提供的ctx_ddl包来同步和优化索引。
1.对于CTXCAT类型的索引，当对基表执行DML操作时，Oracle会自动维护索引。对文档的更改会立即反映在索引中。CTXCAT是交易形式的索引。
索引的同步
在插入、修改和删除基表后同步索引。建议使用sync来同步索引。语法:
ctx _ ddl.sync _索引(
VARCHAR2中的idx_name默认为空
VARCHAR2中的内存默认为空，
VARCHAR2中的part_name默认为空
parallel_degree IN NUMBER默认1)；
Idx_name索引名称
Memory指定同步索引所需的内存。默认值是系统参数DEFAULT_INDEX_MEMORY。
指定大内存可以加快索引效率和查询速度，索引碎片少。
Part_name同步哪个分区索引。
并行度并行同步索引。设置平行度。
例如:
同步索引myindex:exectx _ DDL . sync _ index(' myindex ')；
实现建议:建议通过oracle的job同步索引。
索引优化
频繁的索引同步会导致上下文索引碎片化。索引碎片严重影响查询的响应速度。可以定期优化索引，减少碎片，减小索引大小，提高查询效率。
从表中删除文本后，Oracle Text会标记删除的文档，但不会立即修改索引。因此，现有的文档信息占用了不必要的空间，导致了额外的查询成本。您必须在完全模式下优化索引，并从索引中删除无效的旧信息。这个过程叫做垃圾处理。当你经常更新和删除表格文本数据时，垃圾处理是必要的。
exec CTX _ DDL . optimize _ index(' myidx '，' full ')；
建议:每天在系统空闲时优化全文索引，提高检索效率。
P.s .时序优化指数
3.定时优化同步域索引。
创建计划任务，定期优化和同步域索引。
SQL & gt创建或替换过程hsp_sync_index为
2开始
3 CTX _ DDL . sync _ index(' id _ cont _ msg ')；
4结束；
5 /
程序已创建。
耗时:00:00:00.08
SQL & gt可变工单编号；
SQL & gt开始
2 DBMS_JOB。提交(:作业号，' HSP _ sync _ index()；',
3 SYSDATE，' SYSDATE+(1/24/4)')；
4提交；
5结束；
6 /
PL/SQL过程已成功完成。
耗时:00:00:00.27
SQL & gt将过程hsp_optimize_index创建或替换为
2开始
3 CTX _ DDL . optimize _ index(' id _ cont _ msg '，' FULL ')；
4结束；
5 /
SQL & gt可变工单编号；
SQL & gt开始
2 DBMS_JOB。SUBMIT(:jobno，' HSP _ optimize _ index()；',
3 SYSDATE，' SYSDATE+1 ')；
4提交；
5结束；
6 /
程序已创建。
耗时:00:00:00.03
PL/SQL过程已成功完成。
耗时:00:00:00.02
SQL & gt