针对全文索引的混合查询

问题描述

我正在使用SQL Server 2012.表格有一个文本和一个日期列。文本列具有全文索引。该查询针对全文列发出CONTAINS，但它还需要在日期列中包含一个大于条件的条件。我担心SQL Server合并b-tree和全文索引的结果的性能。

在Oracle中，通过包含normal 列（不受全文搜索）转换为全文索引（CTXCAT），因此可以设置如下全文索引：

  ctx_ddl.create_index_set（ 'auction_set'）; 
 ctx_ddl.add_index（'auction_set'，'start_date'）; 
  

，然后像这样查询：

<$ p （玩具狗）|活的动物'，'start_date> ###'）> 0

是否可以将b-tree索引合并到SQL Server的全文索引中？

解决SQL Server中混合（全文本和b-tree）查询场景的最高性能方法是什么？

谢谢。

解决方案

我不相信你会遇到任何问题，如果我理解你的问题正确。我经常将全文和b-tree结合起来，并取得很好的效果。当进行全文搜索时，它会将每个分隔的term看作索引，就像只有一个词的索引列（给出或采用一些sql统计信息）一样。无论哪种方式，SQL都必须找出它的执行路径。全文搜索不支持比较整数/日期值 - 更多用于匹配分隔数据字符串。

我想你会希望继续使用b-tree概念的效率。尽管在使用LIKE解析/比较字符串的情况下，全文目录索引搜索对我来说似乎是一个更加全面的搜索。

我的工作是：

  SELECT * FROM MyTable 
 WHERE CONTAINS（columnName，'玩具狗或活动物种）
 AND start_date> ###; 
  

（请参阅）

PS当使用全文索引整数数据时，关闭停止列表，以便在目录索引中不会忽略这些值。

希望有任何帮助！ （没人回答，所以我想我会给我的经验）

I am using SQL Server 2012. A table has a text and a date column. The text column has a full-text index. The query issues CONTAINS against the full-text column but it also needs to include a greater-than condition on the date column. I am concerned about performance of SQL Server merging results from b-tree and full-text indices.

In Oracle, the performance aspect of this scenario is addressed by including "normal" columns (that are not subject to full-text search) into a full-text index (CTXCAT), so it's possible to set up a full-text index like that:

ctx_ddl.create_index_set('auction_set');
ctx_ddl.add_index       ('auction_set', 'start_date');

and then query like that:

WHERE CATSEARCH (item_desc, '(toy dog) | "live animal"', 'start_date > ###') > 0

Is it possible to combine b-tree indices into full-text indices in SQL Server?

What is the most performant way to address the scenario of mixed (full-text & b-tree) querying in SQL Server?

Thanks.

解决方案

I don't believe you should experience any problem, if I understand your question correctly. I often combine full-text and b-tree with great results. When the full-text search is conducted, it is looking at each delimited "term" as an index, just as it would an indexed column with only one term (give or take some sql statistics). Either way, SQL must figure out its execution path. Full Text Search doesn't favor comparing integer/date values--more for matching strings of delimited data.

I would imagine you'd want to keep using the efficiency of the b-tree concept to your advantage. The full-text catalog index search appears to me to be a much more round-about search, although much more advantageous in situations using "LIKE" to parse/compare strings.

What I do is:

 SELECT * FROM MyTable
  WHERE CONTAINS(columnName, '"Toy Dog" OR "live animal"')
    AND start_date > ###;

(see this msdn article for syntax info)

P.S. when full-text-indexing integer data, turn off the stoplist so that those values aren't ignored in the catalog indexing.

Hope any of that helps! (Nobody has answered so I thought I'd give my experience)

这篇关于针对全文索引的混合查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！