sql - Oracle:带条件的全文搜索

Question

我创建了一个 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 s。

理想情况下，我宁愿只搜索带有 group_id = 43 的项目。 ，所以我想要一个这样的索引:

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

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

有没有办法在 Oracle 中做这样的事情(如果这很重要，我正在使用 10g)？

编辑(澄清)

考虑一个包含一百万行和以下两列的表， A和 B , 都是数字。假设 A 有 500 个不同的值和 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") index 查找特定 group_id 下的文本索引并扫描我需要的特定行/行的文本索引。不需要扫描和检查或加入，就像在 (A,B) 上使用索引一样。 .

Answer 1

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 索引(“位图和”？)连接起来，但性能很差。