context optimization?

From: Dave Bernick <bernz_at_bernztech.org>
Date: Wed, 22 Dec 1999 14:49:28 -0500
Message-ID: <38612B48.4D1A3109@bernztech.org>

I'm using context, which i'm fairly new at. I have multiple contains() statements in a single query. If i use AND to

separate them, the query takes mere miliseconds (which is what i really want) but if i separate with or, it takes about 12 seconds, which strikes me as odd. without the contains() statements, it takes mere  miliseconds as well.
Can anyone help me with optimizing it as EXPLAIN PLAN doesn't recognize  contains()?
this query WORKS it just seems to take an abnormal ammount of time to work.
the query is as follows...

select

tab_forum.for_name,
tab_question.for_id,
tab_question.qtn_id,
tab_question.cus_id,
tab_question.qtn_subject,
tab_answer.ans_value,
tab_answer.exp_id,
tab_answer.ans_id,

round(avg(tab_billing.bill_expertscore),2) as expscore, count(tab_billing.ans_id) as count
from

tab_forum,
tab_question,
tab_answer,
tab_billing

where
tab_forum.for_id = tab_question.for_id
and (contains(tab_question.qtn_text,'%test%',10) >0 or contains(tab_answer.ans_text,'%test%',20) > 0)

and tab_question.qtn_id = tab_answer.qtn_id
and tab_answer.ans_id = tab_billing.ans_id
and tab_question.qtn_private = 2
and tab_billing.bill_expertscore >= 1
and tab_billing.bill_expertscore <= 10
and tab_forum.for_id in

(select for_id from tab_permission where grp_id in (select grp_id from tab_groupuser where tab_groupuser.usr_id = 'guest' and tab_groupuser.gus_who = 2))
group by tab_forum.for_name,

tab_question.for_id,
tab_question.qtn_id,
tab_question.cus_id,
tab_question.qtn_subject,
tab_answer.ans_value,
tab_answer.exp_id,
tab_answer.ans_id

Received on Wed Dec 22 1999 - 13:49:28 CST