This discussion is archived
5 Replies Latest reply: May 15, 2013 2:04 PM by Barbara Boehmer RSS

wildcard query expansion results in too many terms

user13780543 Newbie
Currently Being Moderated
Hi.

In oracle Text with contains query , the search with the keyword '%FARRO AL NAT%' returns too many terms error but with an addition of character '%FARRO AL NATU%' produces the result. But the no. of records for both the search strings are same.

Could anyone explain, what is the exact issue here.
  • 1. Re: wildcard query expansion results in too many terms
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    user13780543 wrote:
    Hi.

    In oracle Text with contains query , the search with the keyword '%FARRO AL NAT%' returns too many terms error but with an addition of character '%FARRO AL NATU%' produces the result. But the no. of records for both the search strings are same.

    Could anyone explain, what is the exact issue here.
    Even though the rows containing '%FARRO AL NAT%' and the rows containing '%FARRO AL NATU%' are the same, there are apparently more rows containing 'NAT%' than 'NATU%'. It is the expansion of the individual term that causes the error, not the whole search string.
  • 2. Re: wildcard query expansion results in too many terms
    user13780543 Newbie
    Currently Being Moderated
    Thanks for the reply,Barbara .

    So, Will it work in such a way that it will get the results for all the individual terms in the search string and get the final result with the AND operation ?

    So, if i need to get the result of the whole search term, will it be possible?
  • 3. Re: wildcard query expansion results in too many terms
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    user13780543 wrote:
    Thanks for the reply,Barbara .

    So, Will it work in such a way that it will get the results for all the individual terms in the search string and get the final result with the AND operation ?

    So, if i need to get the result of the whole search term, will it be possible?
    Using AND won't make a difference, as it is still the expansion of the individual term that raises the error. However, you can change the wildcard_maxterms setting, but there are drawbacks. Please see the excerpt from the online documentation below regarding setting wildcard_maxterms.

    "The maximum value is 50000 and the default value is 20000. If you specify a value of 0, then the number of wildcard expansions will be unbounded.Note that when set to 0, the system may run out of memory due to the high number of wildcard expansions."
  • 4. Re: wildcard query expansion results in too many terms
    user13780543 Newbie
    Currently Being Moderated
    Thanks for your reply.

    I understood your explanation.

    For the below input values

    %FARRO AL N%
    %FARRO AL NA%
    %FARRO AL NAT%

    the system throws the error "wildcard query expansion resulted in too many terms" because the last term has very less no. of characters.
    But I'm able to get results when I try with %FARRO AL NATU% as the system is searching for matches for each word (like FARRO, Al, NATU separately)
    Is there a way that I can make the system search for the whole string instead searching for matches for each word ?

    I have tried using the escaping character {}, it seems to work fine for some scenarios only.

    Thanks.
  • 5. Re: wildcard query expansion results in too many terms
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    As I said previously, you can set the wildcard_maxterms to 0. You can click on the link below for an example of setting the wildcard_maxterms.

    http://docs.oracle.com/cd/E11882_01/text.112/e24436/cdatadic.htm#CCREF2027

    Alternatively, you can just use LIKE or INSTR with a regular index, instead of Oracle Text, if all you need to do is search for a sub-string and you don't need any of the features of Oracle Text.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points