Re: CATSEARCH understanding

RogBaker_at_gmail.com schreef:
> 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?
>

Sorry, I just see one wildcard, namely on ssn.

And CATSEARCH seems the wrong index CTXCAT seems the way to go, maybe combined with a composed index (using a CLOB and a stored procedure). Once you get these working, they work like a charm. You would end up with a code snippet like: select * from TBLREMEDIATION
where contains(ID, '12345678') > 0

ID would be the column you define your index on - as your stored procedure concatenates all fields you want to inspect, you can choose any column to define your index on.

My personal preference is to use a column that has absolutely zero, none, nada reference to the data you want to search on, because of the special nature of the beast. ID would be such a column (just holding a sequence generated number)

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...