Database Software

Dear all,

I'm trying to set the following preference to create my index:

begin
ctx_ddl.create_preference('substring_pref', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('substring_pref','PREFIX_INDEX','TRUE');
ctx_ddl.set_attribute('substring_pref','PREFIX_MIN_LENGTH',3);
ctx_ddl.set_attribute('substring_pref','PREFIX_MAX_LENGTH',15);
ctx_ddl.set_attribute('substring_pref','SUBSTRING_INDEX','TRUE');
end;

create index artikel_test_idx
on artikel_test (art_content)
indextype is ctxsys.context
parameters ( 'WORDLIST substring_pref' )

The index is created sucessfully.
Here is the data I have in my table:

ART_INHOUD ART_TITEL ART_CONTENT
---------- --------------- -------------------------------
1 Titel1 prion alzheimer
2 Titel2 prion word1 alzheimer
3 Titel3 prion word2 word3 alzheimer
4 Titel4 prion
5 Titel5 alzheimer
6 Titel6 alzheimer prion
7 Titel7 alzheimer word1 prion
8 Titel8 something else
9 Titel9 prion alzheimer prion alzheimer
10 Title10 i am theree
11 Title11 contract initiation work
12 Title12 oracle university else

12 rows selected.

I test the substring feature by searching for "alz". It should returns me all lines that contains "alzheimer". Am I right? But the result show nothing:

select art_titel, score(1) as score
from artikel_test
where contains (art_content, 'alz', 1) > 0

no rows selected

If I search for the entire word, it works:

select art_titel, score(1) as score
from artikel_test
where contains (art_content, 'alzheimer', 1) > 0;

ART_TITEL SCORE
--------------- ----------
Titel9 7
Titel7 4
Titel6 4
Titel5 4
Titel3 4
Titel2 4
Titel1 4

7 rows selected.

I look at the table DR$ARTIKEL_TEST_IDX$I and all works are in it:
TOKEN_TEXT
----------------------------------------------------------------
ALZ
ALZH
ALZHE
ALZHEI
ALZHEIM
ALZHEIME
ALZHEIMER
ALZHEIMER
AM
CON
CONT
CONTR
CONTRA
CONTRAC
CONTRACT
CONTRACT
...

Why there is no result when I search for 'alz'?

Regards,
Yanick

Message was edited by:
yankee75