Oracle：带条件的全文搜索

admin

Oracle：带条件的全文搜索

sql

我创建了如下的Oracle Text索引：

create index my_idx on my_table (text) indextype is ctxsys.context;

然后，我可以执行以下操作：

select * from my_table where contains(text, '%blah%') > 0;

但是，可以说我们在此表中还有另一列，例如group_id，我想改为执行以下查询：

select * from my_table where contains(text, '%blah%') > 0 and group_id = 43;

使用上述索引，Oracle将必须搜索包含的所有项目，'blah'然后检查所有group_id。

理想情况下，我宁愿只使用来搜索项目group_id = 43，因此我想要这样的索引：

create index my_idx on my_table (group_id, text) indextype is ctxsys.context;

有点像普通索引，因此可以对每个进行单独的文本搜索group_id。

有没有一种方法可以在Oracle中做这样的事情（如果很重要，我将使用10g）？

编辑（说明）

考虑一个具有一百万行和以下两列的表，A以及B均为数字。可以说有500个不同的值A和2000个不同的值B，并且每一行都是唯一的。

现在让我们考虑 select ... where A = x and B = y

在索引A和B单独至于我可以告诉做一个索引搜索B，它会返回500个的不同行，然后做一个连接/扫描这些行。无论如何，至少要查看500行（除了数据库很幸运并能尽早找到所需的行之外）。

索引启用(A,B)更为有效，但它会在一次索引搜索中找到一行。

group_id我觉得在文本上放置单独的索引只会给查询生成器留下两个选项。

（1）使用group_id索引，并在所有结果行中扫描文本。
（2）使用文本索引，并在所有结果行中扫描group_id。
（3）使用两个索引，并进行联接。

而我想要：

（4）使用(group_id, "text")索引查找特定索引下的group_id文本索引，然后扫描该文本索引以查找所需的特定行/行。无需扫描，检查或合并，就像在上使用索引时一样(A,B)。

阅读 220

2021-05-10

共1个答案

admin

Oracle文本

1-您可以通过使用FILTER
BY创建CONTEXT索引来提高性能：

create index my_idx on my_table(text) indextype is ctxsys.context filter by group_id;

在我的测试中，filter by绝对可以提高性能，但是仅在group_id上使用btree索引仍然稍快一些。

2-CTXCAT索引使用“子索引”，并且工作方式类似于多列索引。这似乎是您要寻找的选择（4）：

begin
  ctx_ddl.create_index_set('my_table_index_set');
  ctx_ddl.add_index('my_table_index_set', 'group_id');
end;
/

create index my_idx2 on my_table(text) indextype is ctxsys.ctxcat
    parameters('index set my_table_index_set');

select * from my_table where catsearch(text, 'blah', 'group_id = 43') > 0

这可能是最快的方法。对与您的A和B场景类似的120MB随机文本使用以上查询，只需要18个一致的获取即可。但不利的一面是，创建CTXCAT索引花费了将近11分钟并占用了1.8GB的空间。

（注意：Oracle
Text在这里似乎可以正常工作，但是我对Text不熟悉，我不能保证这不是对@NullUserException之类的这些索引的不当使用。）

多列索引与索引联接

对于您在编辑中描述的情况，通常
在（A，B）上使用索引与在A和B上连接单独的索引之间不会有显着差异。我使用与您所描述的数据和索引相似的数据构建了一些测试对于多列索引，join只需要7个一致的获取，而2个一致的获取。

这样做的原因是因为Oracle检索块中的数据。一个块通常为8K，并且已经对索引块进行了排序，因此您可以在几个块中容纳500到2000个值。如果您担心性能，通常只需要读取和写入块的IO即可。Oracle是否必须将几千行连接在一起是不重要的CPU时间。

但是，这不适用于Oracle Text索引。您可以将CONTEXT索引与btree索引（“ bitmap and”？）连接起来，但是性能很差。

2021-05-10