Hello,

When i run :

-- operator > 0

select * from mytable where CONTAINS(mycolumn, 'HELLO,WORLD',1) > 0;
select * from mytable where CATSEARCH(mycolumn, 'HELLO | WORLD',NULL)
I get all the lines that contain HELLO or WORLD in mycolumn in both
cases. Great.

-- operator = 0

select * from mytable where CONTAINS(mycolumn, 'HELLO,WORLD',1) = 0;
I get all the lines that do not contain HELLO and do not contain
WORLD in mycolumn at the same time.

However when i run this
select * from mytable where CATSEARCH(mycolumn, 'HELLO | WORLD',NULL)
= 0;
I get all the lines that contain HELLO or WORLD in mycolumn, it does
not work like CONTAINS, it works as if we had put >0.

I've read that catsearch does not compute a score so i understand this
behaviour.
But is it still possible to write the query so that the CATSEARCH
operator retrieves lines that do not contain all the words specified ?

We need ctxcat indexes because they are automatically updated after
DMLs and this is not the case with context indexes.

Thank you in advance

Jean-Michel

So, could you not rewrite using MINUS or a subquery wiith NOT IN or
NOT EXISTS?
e.g.
select * from mytable where rowid not in (select rowid from mytable
where CATSEARCH(mycolumn, 'HELLO | WORLD',NULL) > 0) ?

On Jun 7, 2:39*pm, dombrooks <dombro... (AT) hotmail (DOT) com> wrote:
Thank you.
I was looking for a simpler solution, really something like catsearch
= 0.
(because we will use catsearch in complex joins ..)

I finally got the solution from the oracle support.
=> transactional parameter

create index myidx on mytable(mycolumn)
indextype is ctxsys.context
parameters ('transactional');

There is also the possibility of SYNC on COMMIT but transactional is
better for our need.

Doc ID 249991.1

On Jun 7, 4:26*pm, Jean-Michel S <zejea... (AT) gmail (DOT) com> wrote:
Thus we can use CONTEXT instead of CTXCAT, i mean.