Fixing wildcard expansion resulting in too many terms error (DRG-51030)

full-text-searchoracleoracle-10g

How can I resolve the wildcard_maxterm problem in Oracle 10g?

My index creation syntax is:

begin
    ctx_ddl.drop_preference('SUBSTRING_PREF');
end;
/

begin
    ctx_ddl.create_preference('SUBSTRING_PREF', 'BASIC_WORDLIST');
    ctx_ddl.set_attribute('SUBSTRING_PREF', 'SUBSTRING_INDEX','TRUE');
end;
/

begin
    ctx_ddl.drop_preference('bus_obj1_lexer');
end;
/

begin
    ctx_ddl.create_preference('bus_obj1_lexer','BASIC_LEXER');
    ctx_ddl.set_attribute('bus_obj1_lexer', 'base_letter', 'YES');
    ctx_ddl.set_attribute('bus_obj1_lexer', 'mixed_case', 'YES' );
    ctx_ddl.set_attribute('bus_obj1_lexer','printjoins', ';,@_');  
    ctx_ddl.set_attribute('bus_obj1_lexer','skipjoins', '-'); 
end;
/

create index BUS_OBJ_FTS_Name on BUSINESS_OBJECTS1(name)
  indextype is ctxsys.context 
  parameters ('wordlist SUBSTRING_PREF 
               MEMORY 128M 
               DATASTORE CTXSYS.DEFAULT_DATASTORE
               SECTION GROUP CTXSYS.AUTO_SECTION_GROUP
               STOPLIST CTXSYS.EMPTY_STOPLIST                         
               LEXER bus_obj1_lexer                         
               SYNC (ON COMMIT)');

And my query is:

select * from BUSINESS_OBJECTS1 where contains(name,'%WIN%')>0 and rownum<=100;

There are 15 million rows in that table, and more than 50000 rows match that query. I have set wildcard_maxterm=50000 and default=20000 but I'm still getting this error:

DRG-51030: wildcard query expansion resulted in too many terms.

Can anybody hep me how to solve this error?

Best Answer

The wildcard_maxterm is set to the maximum. Actually, the maximum in 10g is 15,000. I think the best approach is to limit the search term. The Oracle error action is to make the query narrower. Here is Oracle's documentation on wildcards (%_). The % means zero or more characters. The _ means match in a single position. And 'WIN' is a broad term for a contains search.

I think you should try a very narrow query and then gradually broaden the query until you find right combinations. Can you remove the % before and after and try a % only at the end? Try to increase the characters before or after WIN. Try multiple queries for more specific terms. Try a fixed number of _ characters instead of a %?

Related Question