Re: Oracle Text Boolean search
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, geraldine_2@xxxxxxxxxxx
- Date: Tue, 21 Sep 2004 10:51:58 +0200
Geraldine,
It's a bit difficult to tell you 'try that' without knowing anything
aboutthe sizes of the tables, selectivity of criteria and existing indices.
If your query takes more and more time to return as you add criteria, it is
likely that your criteria are not extraordinarily efficient. You have
basically two paths for optimization :
1) Making each SELECT more efficient
2) Having fewer SELECTs (fewer UNIONs) and trying to grab more data in one
pass.
For 1), if I assume that you have a composite index on (id, type) which
doesn't seem outlandish, depending on the number of rows returned, writing
select id, name from testtable tab1
where tab1.type='HEADING'
and contains (value, 'a')>1
and (tab1.id, 'BODY') in (select id, type
from testtable tab2
where contains (value, 'b')>1)
may be noticeably faster if the condition in the inner query is more
efficient than the condition in the outer query.
For 2), my instinct would lead me to replace the single contains() above by
asum of contains() (if the sum is not 0 then one at least of the terms is
not0 - OR in other words), both in the outer and inner queries, and I would
add more filtering conditions, probably pretty heavy on decode(), to check
that we just keep some combinations, with probably products of contains() to
implement AND.
Hope that helps,
Stephane Faroult
RoughSea Ltd
http://www.roughsea.com
On Tue, 21 Sep 2004 01:55 , geraldine_2@xxxxxxxxxxx sent:
hi, can someone provide some insights how I can rewrite my boolean search
query more efficiently?
I would like to do a search for something like "(a AND b) or (c AND d) or (e
AND f)..."
The query I have formulated does not scale well when I have multiple AND's
and OR's.
select * from (
( select id, name from testtable tab1
where tab1.type='HEADING'
and contains (value, 'a')>1
AND EXISTS
(select id, name from testtable tab2
where tab1.id=tab2.id
and tab2.type='BODY'
and contains (value, 'b')>1
)
)
UNION
( select id, name from testtable tab1
where tab1.type='BODY'
and contains (value, 'c')>1
AND EXISTS
(select id, name from testtable tab2
where tab1.id=tab2.id
and tab2.type='SUBHEADING'
and contains (value, 'd')>1
)
)
UNION
...
...
and so on....
appreciate any help.
thanks.
geraldine
--
//www.freelists.org/webpage/oracle-l[1]
--- Links ---
1
modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
