Re: Several Contains join by AND OR

Thank you for the reply. Yes I did create a intermedia index correctly using

create index im_rel_index on en_table(relation) indextype is ctxsys.context;
create index im_en1_index on en_table(en1) indextype is ctxsys.context;
create index im_en2_index on en_table(en2) indextype is ctxsys.context;

for some reason a join query of
SELECT * FROM en_table WHERE
(CONTAINS(relation, 'KEY1', 1) > 0 )AND (CONTAINS(en1, 'KEY2', 2) > 0 OR CONTAINS(en2, 'KEY2', 3) > 0);

give a very slow performance.Any way to improve it?

bigjobbies_at_hotmail.com (Ethel Aardvark) wrote in message news:<1a8fec49.0212110649.6ed6b5ef_at_posting.google.com>...
> Are your indexes "indextype is ctxsys.ctxcat"? I have not used text
> searching before, but these seem to be a prerequisite.
>
> Good Luck!
>
> EA
>
> iqboss_at_hotmail.com (Ricky) wrote in message news:<a9c648ec.0212091028.51dde5fc_at_posting.google.com>...
> > I am trying to search record by using contains and I made 3 intermedia
> > text indexes for the column "relation", "en1", "en2". However I found
> > that the speed is very slow when I try to use all 3 indexes join by
> > "AND" "OR". Although the table is a big table having 7 million entries
> > but the searching time should not be like 10+ min for returning 4000
> > items.
> >
> > SELECT * FROM en_table WHERE
> > (CONTAINS(relation, 'KEY1', 1) > 0 )AND
> > (CONTAINS(en1, 'KEY2', 2) > 0 OR CONTAINS(en2, 'KEY2', 3) > 0
> >
> >
> > My question is how to modify the query so that it could speed up the
> > search and do the same thing as I wanted? Thanks a lot. I tried every
> > possible way i could think of to optimize the index(analyze,changing
> > buffer size) but still yielding the same slow performance. PLEASE
> > HELP.
> >
> > Thank you in advance,
> >
> > Wei
Received on Wed Dec 11 2002 - 20:15:57 CET