This discussion is archived
1 Reply Latest reply: Apr 26, 2013 4:14 AM by 1002428 RSS

CTXCAT work slow

1002428 Newbie
Currently Being Moderated
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 :)

Legend

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