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?