Hello,

We have migrated database from Oracle 8i to 10g and found following. Same query, same datastucture and data, but query executions plans are different on Oracle 8i and on 10g. On 8i we are using OPTIMIZER_MODE = CHOOSE and on 10g OPTIMIZER_MODE = all_rows which is default. Resultset on both the database version is same, but since execution plan are different, default sorting is not happening on 10g. So from application we see difference in sorting. Below I have given query and indexes on respective tables, plan on different versions.

Indexes: XIE1MASTERTABLE - (VORGANGSID, LEISTUNG) and XPKKATALOGTABLE - CODE

SELECT MASTERTABLE.EINRICHTUNG, MASTERTABLE.ITBRowID, ZAEHLER, LEISTUNG, ARTIKEL, BEZEICHNUNG, VERABREICHTDATUM, VERABREICHZEIT, ANZAHL, MENGENEINHEIT, DOSIS, DOSIERUNGSEINHEIT,
VERABREICHUNGSART, CHARGENNREINGZWANG, HOSTTRANSAKTIONSID, IstZuUebertragen, MASTERTABLE.PREIS, VERBRAUCHSORT
FROM MASTERTABLE, KATALOGTABLE
WHERE MASTERTABLE.ARTIKEL = CODE (+) AND VORGANGSID = 3769
AND MASTERTABLE.EINRICHTUNG = 'Ein1' ;

On Oracle 8i:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=23 Bytes=2139)
1 0 NESTED LOOPS (OUTER) (Cost=28 Card=23 Bytes=2139)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MASTERTABLE' (Cost=5 Card=23 Bytes=1035)
3 2 INDEX (RANGE SCAN) OF 'XIE1MASTERTABLE' (NON-UNIQUE) (Cost=3 Card=23)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'KATALOGTABLE' (Cost=1 Card=2309 Bytes=110832)
5 4 INDEX (UNIQUE SCAN) OF 'XPKKATALOGTABLE' (UNIQUE)

----------------------------------------------------------------------

On Oracle 10g:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=22 Bytes=2046)
1 0 HASH JOIN (OUTER) (Cost=16 Card=22 Bytes=2046)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MASTERTABLE' (TABLE) (Cost=5 Card=22 Bytes=990)
3 2 INDEX (RANGE SCAN) OF 'XIE1MASTERTABLE' (INDEX) (Cost=3 Card=22)
4 1 TABLE ACCESS (FULL) OF 'KATALOGTABLE' (TABLE) (Cost=10 Card=2309 Bytes=110832)


But when I set 'ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS' on 10g, query changed plan as shown below.

SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=29 Card=22 Bytes=2046)
NESTED LOOPS (OUTER) (Cost=29 Card=22 Bytes=2046)
TABLE ACCESS (BY INDEX ROWID) OF TBLVERBRAUCH (TABLE) (Cost=7 Card=22 Bytes=990)
INDEX (RANGE SCAN) OF XPKVERBRAUCH (INDEX (UNIQUE)) (Cost=3 Card=22)
TABLE ACCESS (BY INDEX ROWID) OF KATARTIKEL (TABLE) (Cost=1 Card=1 Bytes=48)
INDEX (UNIQUE SCAN) OF XPKKATARTIKEL (INDEX (UNIQUE)) (Cost=0 Card=1)

and result was sorted as it is on Oracle 8i.
Does this mean to maintain default sorting of queries we need use optimizer first_rows and not all_rows on 10g? Why all_rows does HASH JOIN (OUTER)than NESTED LOOPS (OUTER).

Why same data structure has different explain plan on 8i and 10g? and to maintain same query plans of 8i on 10g what we need to do?

Please guide us and share your experiences.

Thanks & Regards,

Shailesh