Re: Oracle Text Boolean search

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_at_comcast.net 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

--
http://www.freelists.org/webpage/oracle-l[1]



--- Links ---
   1 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l
--
http://www.freelists.org/webpage/oracle-l