RE: Strange behaviour of optimizer

From: Jain, Akshay <Akshay.Jain_at_cit.com>
Date: Fri, 23 Jun 2000 14:14:07 -0400
Message-Id: <10537.110305@fatcity.com>

How do you feel about=20

  select docnum from MAIN where heading=3D'...'   union
  select docnum from TEXT where contains(inhalt,'...')>0;

This does no join, just one big access each to each table (indexed=20 as appropriate).

The result will be sorted (unless you don't care about duplicates and=20 want to go with UNION ALL), but a sort on a small result set of numbers isn't necessarily awful.

Akshay Jain

---

Newcourt-CIT
Tel. (416) 507-5385

mailto:Akshay.Jain_at_cit.com                     =20
_______________________

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

Received on Fri Jun 23 2000 - 13:14:07 CDT