在Oracle中使用Contains子句时期望准确的结果 [英] Expecting exact results when using contains clause in Oracle

问题描述

我有一个下面的contains子句查询，以获取最佳匹配查询.我在表格中有以下两个值:

I have a below contains clause query to get the best match queries. I have below two values in the table:

1. TRUSTS ACT 1973 and 
2. TRUST ACCOUNTS ACT 1973.

当我使用下面的查询以字符串"TRUST ACT 1973" 进行搜索时，此搜索的实际结果为TRUST ACCOUNTS ACT 1973.但我希望确切的结果为*TRUSTS ACT 1973*.

When I am searching using the below query with the string "TRUST ACT 1973", for this search the actual result comes as TRUST ACCOUNTS ACT 1973. But I am expecting the exact result as *TRUSTS ACT 1973*.

查询:

SELECT
    /*+first_rows(11) index(a fuzzy_leg_nm_idx)*/
    a.unique_legislation_id,
    a.legislation_name,
    a.jurisdiction,
    score(1) sc
  FROM AU_LEG_PARALLEL_FUZZY a  
    WHERE contains (legislation_name, 
    '<query> 
        <textquery lang="ENGLISH" grammar="CONTEXT"> '
         || '<progression>
                <seq>{TRUST} ACCUM {ACT} ACCUM {1973}</seq>
            </progression>
        </textquery>
        <score datatype="INTEGER" algorithm="COUNT"/>
     </query>', 1) > 0
ORDER BY score(1) DESC;

推荐答案

两条记录均与ACCUM查询匹配.假设您使用默认的BASIC_LEXER且没有词干TRUST ACCOUNTS ACT 1973的得分较高，这主要是因为TRUST与您的查询完全匹配.如果将查询更改为{TRUSTS} ACCUM {ACT} ACCUM {1973}，则TRUSTS ACT 1973得分会更高.

Both records match the ACCUM query. Assuming that you used the default BASIC_LEXER with no word stemming TRUST ACCOUNTS ACT 1973 scores higher mostly because TRUST matches exactly to your query. If you change the query to {TRUSTS} ACCUM {ACT} ACCUM {1973} then TRUSTS ACT 1973 would get a higher score.

根据您的要求，您可能需要考虑词组搜索而不是ACCUM，例如CONTAINS(title, '{TRUSTS} {ACT} {1973}') > 0.在这种情况下，短语必须与记录完全匹配.

Depending on your requirements, you might want to consider a phrase search rather than ACCUM, e.g. CONTAINS(title, '{TRUSTS} {ACT} {1973}') > 0. In this case, the phrase has to match exactly to a record.

这篇关于在Oracle中使用Contains子句时期望准确的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！