Re: Cost- vs. Rule-Optimizer

The CBO is not perfect. I pretty much test every SQL I write and use the appropriate hint. If they do away with the RBO, which I doubt, mgmt will just have to buy bigger and faster hardware.

Why does Oracle require that Oracle Financials run in RBO?????

Rene' Kulschewski wrote in message <7pj06e$9k4$1_at_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 Sat Aug 21 1999 - 08:48:57 CDT