搜索Oracle CLOB列的最佳方法是什么？

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的东西的在线变体。有人可以提供一个如何做到这一点的快速示例吗?

3 个解决方案

#1

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对结构化文档更有帮助。

This link 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

#2

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服务调用的返回值,这可能是一个不同的鱼。

#3

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.

似乎工作得很好,但我会做一些性能测试,看看它在负载下的工作情况。

#1