SQL 搜索 Oracle CLOB 列的最佳方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3493213/
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
What is the best way to search an Oracle CLOB column?
提问by Adam Fyles
I need to search a CLOB column and am looking for the best way to do this, I've seen variants online of using the DBMS_LOB package as well as using something called Oracle Text. Can someone provide a quick example of how to do this?
我需要搜索一个 CLOB 列并且正在寻找执行此操作的最佳方法，我在网上看到了使用 DBMS_LOB 包以及使用称为 Oracle Text 的东西的变体。有人可以提供一个如何做到这一点的快速示例吗？
采纳答案by Baski
Oracle Text indexing is the way go. You can use either CONTEXT or CTXRULE index. CONTEXT can be used on unstructured document where CTXRULE is more helpful on structured documents.
Oracle Text 索引是必经之路。您可以使用 CONTEXT 或 CTXRULE 索引。CONTEXT 可用于非结构化文档，其中 CTXRULE 对结构化文档更有帮助。
Thislink will provide more info the index types & syntax.
此链接将提供有关索引类型和语法的更多信息。
The most important factor you need to consider is LEXER & STOPLIST.
您需要考虑的最重要的因素是 LEXER & STOPLIST。
You can also read the posts on asktom.oracle.com
您还可以阅读 asktom.oracle.com 上的帖子
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5533095920114
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5533095920114
回答by Gary Myers
What is in your CLOB and what are you searching for ?
您的 CLOB 中有什么？您在寻找什么？
Oracle Text is good if you are searching for words or phrases (which is probably what you have in a CLOB). Sometimes you'll store something 'strange' in a CLOB, like XML or the return value of a web-service call and that might be a different kettle of fish.
如果您正在搜索单词或短语（这可能是您在 CLOB 中拥有的内容），则 Oracle Text 非常有用。有时您会在 CLOB 中存储一些“奇怪”的东西，比如 XML 或 Web 服务调用的返回值，这可能是另一回事。
回答by BenCourliss
I needed to do this just recently and came up with the following solution (uses Spring JDBC)
我最近需要这样做，并提出了以下解决方案（使用 Spring JDBC）
String sql = "select * from clobtest where dbms_lob.instr(myclob, ? , 1, 1) > 0";
return (String) getSimpleJdbcTemplate().getJdbcOperations().queryForObject(sql, new RowMapper<Object>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String clobText = lobHandler.getClobAsString(rs, "myclob");
return clobText;
}
}, searchText);
Seems to work pretty well, but I'm going to do some performance testing to see how well it works under load.
似乎工作得很好，但我将进行一些性能测试，看看它在负载下的工作情况。
