CATSEARCH understanding

Please help me get a better understading of CATSEARCH. I read the "Oracle TEXT Reference," but I don't seem to get it.

If I search for 123456789 in an CTXCAT indexed field containing the string 888888888:123456789:777777777 it will find it. But it will not find 12345678 .
Does it have smarts and decides the the colons are seperating complete words?
Same thing seems to occur with spaces. If the field contains 'symphony number nine' it will find 'number' but not 'numbe' . The * wildcard seems to work okay for my purposes, unless there is something unforseen that I am not considering.

My ultimate goal is rewrite a query that a developer wrote which is not using an index due to the wildcards on the large VARCHAR2 field. We have a table that has a fields containing individual teacher ids, then another field that contains a string of multiple ids.

something like:

Select * from TBLREMEDIATION where
math_tchname_ssn = '123456789'
or eng_tchname_ssn = '123456789'
or socstud_tchname_ssn = '123456789'
or sci_tchname_ssn = '123456789'
or read_tchname_ssn = '123456789'
or oth_tchname_ssn like '%123456789%' ) order by lastname

Is a CTXCAT index better than a CONTEXT index for this example? Received on Tue Aug 22 2006 - 13:32:10 CDT