Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Optimizer change from 8i-9i
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
Elapsed: 00:00:26.01
Execution Plan
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
Elapsed: 00:00:00.03
Execution Plan
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-lReceived on Wed May 11 2005 - 03:27:20 CDT
![]() |
![]() |