RE: Optimizer change from 8i-9i

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Wed, 11 May 2005 09:22:48 +0200
Message-ID: <7F0C000A3ABA6241A10C9ABF37EEB46D040851@MSXVS01.trivadis.com>

Hi=20

> About the RBO change in 9i, can you show me a test case?

E.g. complex_view_merging could lead to different execution plans. If I run the following statements in 8.1.7 and 9.2.0 I get different = results...
(notice that both database have a "minimal" INIT.ORA that doesn't modify = any optimizer parameter)

ALTER SESSION SET optimizer_mode =3D rule;

SELECT * FROM v$version WHERE rownum =3D 1;

CREATE OR REPLACE VIEW min_zip_v AS
SELECT city, min(zip) zip
FROM addresses
GROUP BY city;

SET TIMING ON
SET AUTOTRACE ON EXP SELECT adr.*
FROM persons prs, addresses adr
WHERE (adr.city, adr.zip) IN (SELECT city, zip FROM min_zip_v) AND adr.prs_id =3D prs.id
AND prs.lastname =3D 'Meier';

BANNER

---

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production

Elapsed: 00:00:26.01

Execution Plan

---

SELECT STATEMENT Optimizer=3DRULE
  NESTED LOOPS
    NESTED LOOPS

      VIEW OF 'MIN_ZIP_V'
        SORT (GROUP BY)
          TABLE ACCESS (FULL) OF 'ADDRESSES'
      TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESSES'
        INDEX (RANGE SCAN) OF 'ADR_CITY' (NON-UNIQUE)
    TABLE ACCESS (BY INDEX ROWID) OF 'PERSONS'
      INDEX (UNIQUE SCAN) OF 'PRS_PK' (UNIQUE)

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

Elapsed: 00:00:00.03

Execution Plan

---

SELECT STATEMENT Optimizer=3DRULE
  FILTER
    SORT (GROUP BY)

      TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESSES'
        NESTED LOOPS
          NESTED LOOPS
            TABLE ACCESS (BY INDEX ROWID) OF 'PERSONS'
              INDEX (RANGE SCAN) OF 'PRS_LASTNAME' (NON-UNIQUE)
            TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESSES'
              INDEX (RANGE SCAN) OF 'ADR_PRS_ID' (NON-UNIQUE)
          INDEX (RANGE SCAN) OF 'ADR_CITY' (NON-UNIQUE)

Therefore don't think that the migration will be easy! Carefully plan it = and, of course, do some load tests.=20

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 11 2005 - 03:27:20 CDT