RE: Optimizer change from 8i-9i
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: "zhu chao" <zhuchao@xxxxxxxxx>
- Date: Wed, 11 May 2005 09:22:48 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
