RE: Strange behaviour of optimizer

I'd suggest trying EXISTS instead of IN, e.g.,

select docnum from MAIN m
where heading=3D'...'=20
or exists
select 'x' from TEXT t
WHERE t.docnum =3D m.docnum
and contains(inhalt,'any text')>0);

Adding an index on text.docnum might help in this circumstance as well.

-----Original Message-----
From: Andreas Jung [mailto:ajung_at_sz-sb.de] Sent: Friday, June 23, 2000 8:15 AM
To: Multiple recipients of list ORACLE-L Subject: Strange behaviour of optimizer

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=3D'...'   select docnum from TEXT where contains(inhalt,'...')>0;

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

select docnum from MAIN where heading=3D'...' 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

--=20

                                _\\|//_
                               (' O-O ')
------------------------------ooO-(_)-Ooo-------------------------------=

---
   Andreas Jung, Saarbr=FCcker Zeitung Verlag und Druckerei GmbH
   Saarbr=FCcker Daten-Innovations-Center
   Untert=FCrkheimerstra=DFe 15, D-66103 Saarbr=FCcken, Germany
   Phone: +49-(0)681-502-1528, Fax: +49-(0)681-502-1509
   Email: ajung_at_sz-sb.de (PGP key available)
------------------------------------------------------------------------=
----
---


--=20

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).