Working of CatSearch

See more:

Oracle

I am trying to find a string "1-2050860-3" through CATSEARCH with 3 different query_string:
1. When I give :

select * from local_part_numbers lpn where catsearch(lpn.part_nbr,'2050860',null)>0;

it work fine.

2. When I give:

select * from local_part_numbers lpn where catsearch(lpn.part_nbr,'050860',null)>0;

it is not fetching any result although 050860 is present inside "1-2050860-3".

3. When I give:

select * from local_part_numbers lpn where catsearch(lpn.part_nbr,'*050860*',null)>0;

it is not fetching any result although 050860 is present inside "1-2050860-3".

The index creation code is as below:

begin
  ctx_ddl.drop_preference('CPRCTXCATSTORE');
  ctx_ddl.create_preference('CPRCTXCATSTORE', 'BASIC_STORAGE');
  ctx_ddl.set_attribute('CPRCTXCATSTORE', 'I_TABLE_CLAUSE', 'tablespace CPR_TEXT_DATA_A');
  ctx_ddl.set_attribute('CPRCTXCATSTORE', 'K_TABLE_CLAUSE', 'tablespace CPR_TEXT_INDEX_A');
  ctx_ddl.set_attribute('CPRCTXCATSTORE', 'R_TABLE_CLAUSE', 'tablespace CPR_TEXT_INDEX_A');
  ctx_ddl.set_attribute('CPRCTXCATSTORE', 'N_TABLE_CLAUSE', 'tablespace CPR_TEXT_INDEX_A');
  ctx_ddl.set_attribute('CPRCTXCATSTORE', 'S_TABLE_CLAUSE', 'tablespace CPR_TEXT_INDEX_A');   
  ctx_ddl.set_attribute('CPRCTXCATSTORE', 'I_INDEX_CLAUSE', 'tablespace CPR_TEXT_INDEX_A');
  ctx_ddl.set_attribute('CPRCTXCATSTORE', 'P_TABLE_CLAUSE', 'tablespace CPR_TEXT_INDEX_A');
  ctx_ddl.set_attribute('CPRCTXCATSTORE', 'I_ROWID_INDEX_CLAUSE', 'tablespace CPR_TEXT_INDEX_A');
 
  ctx_ddl.drop_preference('CPRCTXCAT_IDX_LEX');
  ctx_ddl.create_preference('CPRCTXCAT_IDX_LEX','BASIC_LEXER'); 
  ctx_ddl.set_attribute('CPRCTXCAT_IDX_LEX','SKIPJOINS','`=[];\,./~!@#$%^*()_+{}:"|<>?''');

  ctx_ddl.drop_preference('CPRCTXCAT_PARTNBR_IDX_WDL');
  ctx_ddl.create_preference('CPRCTXCAT_PARTNBR_IDX_WDL','BASIC_WORDLIST');
  ctx_ddl.set_attribute('CPRCTXCAT_PARTNBR_IDX_WDL','WILDCARD_MAXTERMS','50000');
  ctx_ddl.set_attribute('CPRCTXCAT_PARTNBR_IDX_WDL','PREFIX_INDEX','TRUE');
  ctx_ddl.set_attribute('CPRCTXCAT_PARTNBR_IDX_WDL','PREFIX_MIN_LENGTH',5);
  ctx_ddl.set_attribute('CPRCTXCAT_PARTNBR_IDX_WDL','PREFIX_MAX_LENGTH',40);
  ctx_ddl.set_attribute('CPRCTXCAT_PARTNBR_IDX_WDL','SUBSTRING_INDEX','TRUE');
end;


CREATE INDEX LPN_PARTNBR_IDX_CTXCAT on LOCAL_PART_NUMBERS(PART_NBR) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS ('STORAGE CPRCTXCATSTORE LEXER CPRCTXCAT_IDX_LEX WORDLIST CPRCTXCAT_PARTNBR_IDX_WDL');

Please help in this regard. i have similar problem for some other pattern also.

thanks,
naman