Cost- vs. Rule-Optimizer

From: Rene' Kulschewski <rkulschewski8482_at_my-deja.com>
Date: Fri, 20 Aug 1999 07:30:30 GMT
Message-ID: <7pj06e$9k4$1@nnrp1.deja.com>

Hi,

i have the following sql-statement running against 8.0.5/NT:

SELECT W.WSCHEIN_NR,T.DATUM,T2.ID, L.*
FROM WIDA W, TOUR T2,TOURP T, AFTP A, TGUT T1, LAGA L WHERE W.TOUR_ID=T2.ID
AND T2.TOUR_ID=T.TOUR_ID
AND T2.DATUM=T.DATUM
AND T.AFTP_ID=A.ID
AND A.TGUT_ID = T1.ID
AND T1.LAGA_ID = L.ID If i use cost-based optimizing (the tables and indizees are analyzed), the following execution-plan is generated: OPERATION OPTIONS OBJECT_NAME
-------------------- ----------- -----------
SELECT STATEMENT
HASH JOIN
TABLE ACCESS FULL WIDA
HASH JOIN
TABLE ACCESS FULL LAGA
HASH JOIN
TABLE ACCESS FULL TGUT
HASH JOIN
TABLE ACCESS FULL TOUR
MERGE JOIN

SORT                 JOIN
TABLE ACCESS         FULL        TOURP
SORT                 JOIN
TABLE ACCESS         FULL        AFTP

The execution time for this statement will be about 45 sec.

If i use explizit rule-based optimization (via hint /*+ RULE +*/) i get the following plan:

OPERATION OPTIONS OBJECT_NAME
------------------ ------------------ ---------------------
SELECT STATEMENT
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS

TABLE ACCESS       FULL               LAGA
TABLE ACCESS       BY INDEX ROWID     TGUT
INDEX              RANGE SCAN         TGUT_LAGA_ID_IDX
TABLE ACCESS       BY INDEX ROWID     AFTP
INDEX              RANGE SCAN         AFTP_TGUT_IDX
TABLE ACCESS       BY INDEX ROWID     TOURP
INDEX              RANGE SCAN         TOURP_AFTPID_IDX
TABLE ACCESS       BY INDEX ROWID     TOUR
INDEX              RANGE SCAN         TOUR_TOURID_DATUM_IDX
TABLE ACCESS       BY INDEX ROWID     WIDA
INDEX              RANGE SCAN         WIDA_TOURID_IDX

The execution time is about 0,05 sec.

How can this happen ?

Since the Oracle-doc points out, that the rule-based approach in future releases will not be supported, what should i do to get the same results with cost-based optimization as with rule-based optimization ?

Regards
Rene' Kulschewski

---

IVU Traffic Technologies AG, Berlin, Germany

--

IVU Traffic Technologies AG
Bundesallee 88, D-12161 Berlin
Fon: +49.30.85 906-327, Fax: +49.30.85 906-313

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Aug 20 1999 - 02:30:30 CDT