Oracle 使用Unicode全文搜索的正确词法是什么

我正在测试文本搜索在Windows应用程序上的实现，方法是通过开发人员创建和查询一些数据，这些数据是我从系统中的实际文档加载的。尽管我在SQL MSS和Oracle方面都有多年的查询经验，但此全文索引对我来说是全新的。我们目前将一些文件附件作为base64字符串存储在NCLOB中，因为数据可以是任何类型的文件。展望未来，我们计划使用varbinary(max) 和blob。MSSQL对此相当直截了当，并检测存储在数据库中的pdf中的语言，并返回我期望的阿拉伯语，中文，英语和法语的结果。Oracle也返回结果，但我对我似乎需要的词法器感到困惑。如果我用世界或自动词法器创建索引，当使用文档中已知单词的文档语言查询时，我只获得单字节语言的结果; 如果我使用中文词法器，我将获得所有4种语言的结果。请注意，我已经尝试使用和不使用索引参数中指定的语言和字符集列，并且我似乎在没有这些列的情况下获得了正确的结果。


我期望 “世界” 的名称暗示比 “中文” 更大的字符集。这是预期的中文词典的结果，还是我在使用世界词典时做错了？


-仅返回英语和法语结果

exec ctx_ddl.create_preference('MYLEXER', 'world_lexer');

CREATE INDEX my_docs_doc_idx ON my_docs(doc)
INDEXTYPE IS CTXSYS.CONTEXT
parameters( 'LEXER MYLEXER');


-- RETURNS ARABIC, CHINESE, ENGLISH AND FRENCH RESULTS
exec ctx_ddl.create_preference<('CHINESE', 'CHINESE_LEXER');

CREATE INDEX my_docs_doc_idx ON my_docs(doc)
INDEXTYPE IS CTXSYS.CONTEXT
parameters( 'LEXER CHINESE');

Oracle文本的词法器的内部工作原理超出了我的知识范围。因此，我联系了甲骨文文本PM的罗杰·福特 (Roger Ford)。

这是他的回应:

The WORLD_LEXER should work with any language but is not particularly clever about how it processes those languages. It treats Western languages, Arabic and Hebrew as words, and pictographic languages (Chinese, Japanese and Korean) as individual characters.

AUTO_LEXER is much cleverer (can automatically generate linguistic stems in Arabic, for example) but needs a decent chunk of text (preferably a couple of paragraphs) in order to correctly identify the language.

CHINESE_LEXER is primarily intended for Chinese text (no suprises there) but is also intended to work for Western languages embedded in the Chinese text. Probably why it works with English and French. I'm quite surprised it works for Arabic as well, though.

It's hard to say exactly what's going wrong here with WORLD_LEXER and AUTO_LEXER - both should work - but I'm somewhat concerned by "storing some file attachments in NCLOB as a base64 string". To be honest, I'm surprised that works at all. If the documents are PDF, they should be in a BLOB column of the database in their original binary form. Oracle Text will then filter the indexable text from the PDF files and index it.

You also need to be careful when entering the queries, to ensure that any multibyte characters are correctly interpreted by Oracle. In SQL Developer, this is usually straightforward - if the character displays properly it's probably correct. If you're using SQL*Plus or a driver such as JDBC, you need to make sure the client or driver character set settings are correct and match the character set of any input file or strings you might be using.