问题 Oracle：带条件的全文搜索
我创建了一个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") index用于查找特定的文本索引 group_id 并扫描我需要的特定行/行的文本索引。不需要扫描和检查或加入，就像使用索引时一样 (A,B)。
7918
2017-09-09 06:54
起源
答案:
Oracle Text
1 - 您可以通过创建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
这可能是最快的方法。对120MB的随机文本使用上述查询，类似于你的A和B场景，只需要18个一致的获取。但在不利方面，创建CTXCAT索引需要大约11分钟，并使用1.8GB的空间。
（注意：Oracle Text似乎在这里工作正常，但我不熟悉Text，我不能保证这不是对@NullUserException这些索引的不当使用。）
多列索引与索引联接
对于您在编辑中描述的情况， 一般 使用（A，B）上的索引和加入A和B上的单独索引之间没有显着差异。我使用类似于您描述的数据构建了一些测试，并且索引连接仅需要7个一致获取而不是2个一致获取对于多列索引。
这是因为Oracle以块的形式检索数据。块通常为8K，并且索引块已经排序，因此您可以在几个块中拟合500到2000个值。如果您担心性能，通常读取和写入块的IO是唯一重要的。 Oracle是否必须将几千行连接在一起是一个无关紧要的CPU时间。
但是，这不适用于Oracle Text索引。您可以使用btree索引（“位图和”？）加入CONTEXT索引，但性能很差。
8
2017-09-16 04:35
我给了一个索引 group_id 看看这是否足够好。您没有说我们正在讨论的行数或您需要的性能。
请记住，处理谓词的顺序不一定是您在查询中编写谓词的顺序。除非你有真正的理由，否则不要试图超越优化器。
1
2017-09-10 14:53
简洁版本： 没有必要这样做。查询优化器足够聪明，可以决定选择数据的最佳方式。只需创建一个btree索引 group_id，即：
CREATE INDEX my_group_idx ON my_table (group_id);
长版： 我创建了一个脚本（testperf.sql）插入136行虚拟数据。
DESC my_table;
Name Null Type
-------- -------- ---------
ID NOT NULL NUMBER(4)
GROUP_ID NUMBER(4)
TEXT CLOB
有一个btree索引 group_id。要确保实际使用索引，请以dba用户身份运行：
EXEC DBMS_STATS.GATHER_TABLE_STATS('<YOUR USER HERE>', 'MY_TABLE', cascade=>TRUE);
这是每行多少行 group_id 有和相应的百分比：
GROUP_ID COUNT PCT
---------------------- ---------------------- ----------------------
1 1 1
2 2 1
3 4 3
4 8 6
5 16 12
6 32 24
7 64 47
8 9 7
请注意，查询优化器仅在它认为这是一个好主意时才会使用索引 - 也就是说，您正在检索最多一定百分比的行。因此，如果您要求查询计划：
SELECT * FROM my_table WHERE group_id = 1;
SELECT * FROM my_table WHERE group_id = 7;
您将看到，对于第一个查询，它将使用索引，而对于第二个查询，它将执行全表扫描，因为当索引有太多行无法生效时 group_id = 7。
现在，考虑一个不同的条件 - WHERE group_id = Y AND text LIKE '%blah%' （因为我不太熟悉 ctxsys.context）。
SELECT * FROM my_table WHERE group_id = 1 AND text LIKE '%ipsum%';
查看查询计划，您将看到它 将 使用索引 group_id。请注意，您的条件顺序并不重要：
SELECT * FROM my_table WHERE text LIKE '%ipsum%' AND group_id = 1;
生成相同的查询计划。如果您尝试运行相同的查询 group_id = 7，你会看到它回到全表扫描：
SELECT * FROM my_table WHERE group_id = 7 AND text LIKE '%ipsum%';
请注意，Oracle每天自动收集统计信息（计划每晚和周末运行），以持续提高查询优化器的有效性。简而言之，Oracle尽力优化优化器，因此您不必这样做。
1
2017-09-11 16:49
我手边没有Oracle实例进行测试，并且没有在Oracle中使用全文索引，但我通常都有良好的性能 内联视图，这可能是你想到的那种索引的替代品。以下语法是否合法 包括（） 参与？
此内联视图可以获取组43中行的PK值：
(
select T.pkcol
from T
where group = 43
)
如果组具有正常索引，并且基数不低，则应该快速获取此集合。然后你再次用T加入内集：
select * from T
inner join
(
select T.pkcol
from T
where group = 43
) as MyGroup
on T.pkcol = MyGroup.pkcol
where contains(text, '%blah%') > 0
希望优化器能够使用PK索引来优化连接，然后应用它 包含 谓词只对组43行。
0
2017-09-10 12:41