1 Reply Latest reply: Apr 26, 2013 6:14 AM by 1002428 RSS

    CTXCAT work slow

    1002428
      Hi, I'm using oracle 10.2.0.3.0. I have table:

      create table addresses(
      text varchar2(2000),
      exp_date number,
      type number(1)
      *)*

      Table has about 500 000 rows and I created a text index using this parameters:

      ctx_ddl.create_preference('adr_lexer', 'BASIC_LEXER');
      ctx_ddl.set_attribute('adr_lexer', 'printjoins', '.-');
      ctx_ddl.set_attribute('adr_lexer', 'base_letter', 'YES');
      ctx_ddl.create_preference('adr_wordlist', 'BASIC_WORDLIST');
      ctx_ddl.set_attribute('adr_wordlist','PREFIX_INDEX','TRUE');
      ctx_ddl.set_attribute('adr_wordlist','PREFIX_MIN_LENGTH', '1');
      ctx_ddl.set_attribute('adr_wordlist','PREFIX_MAX_LENGTH', '64');
      ctx_ddl.set_attribute('adr_wordlist','STEMMER', 'NULL');
      ctx_ddl.set_attribute('adr_wordlist','wildcard_maxterms', '40000');
      CTX_DDL.CREATE_INDEX_SET('adr_index_set');
      CTX_DDL.ADD_INDEX('adr_index_set','exp_date');
      CTX_DDL.ADD_INDEX('adr_index_set','type');

      CREATE INDEX adr_text_ind ON addresses(text) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS ('index set adr_index_set lexer adr_lexer wordlist adr_wordlist')

      Most rows have text like city street number and most rows have the same city and many of rows have number like +1*+. When I'm run query:

      select /* FIRST_ROWS(10) */ text from addresses where CATSEARCH(text, x, 'exp_date = 0 and type = 1 ')> 0

      with x = 'street*' everything is ok - query run about 500 ms. But when I run query with x = 'city*' or x = 'city 1*'+ it taks about 60 s. Prblem occurs when wildcard in x pass to most of rows or when in x are many words that pass to meny rows. I run this select in procedure that fetch only 10 rows. Do you have any idea to speed up the index?

      Regards, Maciek


      Ps. Sory from my English :)