Re: Strange behaviour of optimizer

From: Chuck Hamilton <chuck_hamilton_at_yahoo.com>
Date: Fri, 23 Jun 2000 06:56:41 -0700 (PDT)
Message-Id: <10537.110261@fatcity.com>

--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_at_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_at_sz-sb.de (PGP key available)

--

Author: Andreas Jung
INET: ajung_at_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_at_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

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 <<A href="mailto:ajung_at_sz-sb.de">ajung_at_sz-sb.de</A>> wrote:

<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_at_sz-sb.de (PGP key available)<BR>-------------------------------------------------------------------------------<BR>-- <BR>Author: Andreas Jung<BR>INET: ajung_at_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_at_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 Yahoo! Mail - Free email you can access from anywhere! Received on Fri Jun 23 2000 - 08:56:41 CDT