Message-Id: <10537.110261@fatcity.com>
From: Chuck Hamilton <chuck_hamilton@yahoo.com>
Date: Fri, 23 Jun 2000 06:56:41 -0700 (PDT)
Subject: Re: Strange behaviour of optimizer

--0-1681692777-961768601=:27167
Content-Type: text/plain; charset=us-ascii


 It's probably because of the IN operator. I avoid it as much as possible. Try rewriting the query as a join.

select docnum
from main m, text t
where m.docnum = t.docnum
and (m.heading = '...' or contains(t.inhalt,'any text') > 0);

  Andreas Jung <ajung@sz-sb.de> wrote: 

I have the following tables:

MAIN(docnum number(10) primary key, heading varchar2(4)...)
TEXT(docnum number(10) primary key, inhalt varchar2(4000))

"heading" is normally index, "inhalt" is indexed with a context.ctxsys index.

Searches like
select docnum from MAIN where heading='...'
select docnum from TEXT where contains(inhalt,'...')>0;

work fine and fast. However the following query take minutes:

select docnum from MAIN where heading='...' or docnum IN ( select docnum
from TEXT where contains(inhalt,'any text')>0);

I am using Oracle 8k and the CBO (all tables and indexes are analyzed). 
Any idea why this happens ?

Andreas






-- 
_\\|//_
(' O-O ')
------------------------------ooO-(_)-Ooo--------------------------------------
Andreas Jung, Saarbr|cker Zeitung Verlag und Druckerei GmbH
Saarbr|cker Daten-Innovations-Center
Untert|rkheimerstra_e 15, D-66103 Saarbr|cken, Germany
Phone: +49-(0)681-502-1528, Fax: +49-(0)681-502-1509
Email: ajung@sz-sb.de (PGP key available)
-------------------------------------------------------------------------------
-- 
Author: Andreas Jung
INET: ajung@sz-sb.de

Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).


---------------------------------
Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere!
--0-1681692777-961768601=:27167
Content-Type: text/html; charset=us-ascii

<P> It's probably because of the IN operator. I avoid it as much as possible. Try rewriting the query as a join.<BR><BR>select docnum<BR>from main m, text t<BR>where m.docnum = t.docnum<BR>and (m.heading&nbsp;= '...'&nbsp;or contains(t.inhalt,'any text') &gt; 0);<BR><BR>&nbsp; <B><I>Andreas Jung &lt;<A href="mailto:ajung@sz-sb.de">ajung@sz-sb.de</A>&gt;</I></B> wrote: <BR></P>
<BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px">I have the following tables:<BR><BR>MAIN(docnum number(10) primary key, heading varchar2(4)...)<BR>TEXT(docnum number(10) primary key, inhalt varchar2(4000))<BR><BR>"heading" is normally index, "inhalt" is indexed with a context.ctxsys index.<BR><BR>Searches like<BR>select docnum from MAIN where heading='...'<BR>select docnum from TEXT where contains(inhalt,'...')&gt;0;<BR><BR>work fine and fast. However the following query take minutes:<BR><BR>select docnum from MAIN where heading='...' or docnum IN ( select docnum<BR>from TEXT where contains(inhalt,'any text')&gt;0);<BR><BR>I am using Oracle 8k and the CBO (all tables and indexes are analyzed). <BR>Any idea why this happens ?<BR><BR>Andreas<BR><BR><BR><BR><BR><BR><BR>-- <BR>_\\|//_<BR>(' O-O ')<BR>------------------------------ooO-(_)-Ooo--------------------------------------<BR>Andreas Jung, Saarbr|cker Zeitung Verlag und Druckerei GmbH<!
BR>Saarbr|cker Daten-Innovations-Center<BR>Untert|rkheimerstra_e 15, D-66103 Saarbr|cken, Germany<BR>Phone: +49-(0)681-502-1528, Fax: +49-(0)681-502-1509<BR>Email: ajung@sz-sb.de (PGP key available)<BR>-------------------------------------------------------------------------------<BR>-- <BR>Author: Andreas Jung<BR>INET: ajung@sz-sb.de<BR><BR>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051<BR>San Diego, California -- Public Internet access / Mailing Lists<BR>--------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).</BLOCKQUOTE><p><br><hr size=1><b>Do You Yahoo!?</b><br>
Get <a href="http://mail.yahoo.com/">Yahoo! Mail</a> - Free email you can access from anywhere!