Re: "order by " clause confuses CBO

I just went thru the same thing.

take alook at ur optimizer mode, see if it happens to be defaulted to first_rows, once i set mine back to choose, all was well with the order by clause.

joe

Andreas Jung wrote:

> The following simple join retrieves all rows very quick and the execution plan
> looks fine.
>
> SQL> select docnum from ojs_main where docnum in (select unique docnum from ojs_de where contains(inhalt,'Tokio or tokyo')>0);
>
> 4027 rows selected.
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=2183 Card=75
> 18554 Bytes=11488350512)
>
> 1 0 NESTED LOOPS (Cost=2183 Card=7518554 Bytes=11488350512)
> 2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'OJS_DE' (Cost=2
> 6 Card=2156 Bytes=3266340)
>
> 3 2 DOMAIN INDEX OF 'OJS_DE_IDX' (Cost=24)
> 4 1 INDEX (UNIQUE SCAN) OF 'OJS_MAIN_PK' (UNIQUE) (Cost=1 Ca
> rd=348727 Bytes=4533451)
>
> When I add an "order by" clause Oracle 8i need much more longer than the same
> query above and the execution is a complete other one. docnum is of type NUMBER
> and is the primary key in both tables. All tables and indexes are fully
> analyzied. I also tried to replace the query below by a join over both tables
> but without any success. I received the same execution plan. Also working with
> hints did not work. Any idea how to get this query optimized ?
>
> SQL> select docnum from ojs_main where docnum in (select unique docnum from ojs_de where contains(inhalt,'Tokio or tokyo')>0) order by docnum;
>
> 4027 rows selected.
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=2431 Card=75
> 18554 Bytes=11488350512)
>
> 1 0 MERGE JOIN (Cost=2431 Card=7518554 Bytes=11488350512)
> 2 1 INDEX (FULL SCAN) OF 'OJS_MAIN_PK' (UNIQUE) (Cost=1440 C
> ard=348727 Bytes=4533451)
>
> 3 1 SORT (JOIN) (Cost=965 Card=2156 Bytes=3266340)
> 4 3 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'OJS_DE' (Cost
> =26 Card=2156 Bytes=3266340)
>
> 5 4 DOMAIN INDEX OF 'OJS_DE_IDX' (Cost=24 Card=2156)
>
> --
> _\\|//_
> (' O-O ')
> ------------------------------ooO-(_)-Ooo--------------------------------------
> Andreas Jung, Saarbrücker Zeitung Verlag und Druckerei GmbH
> Multimedia EU/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
Received on Wed Jul 26 2000 - 09:52:08 CDT