Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Request in 9i slower than in 8i ...
Hi,
For a migration from 8i to 9i
a request is slower in 9i than 8i ( x5 )
the excution plans are different
why ? and how can I speed my request in 9i as it was in 8i ??
Thanks in advance...
Here is my request:
( env: oracle 8.1.7.4/AIX 4.3.3 , 9.2.0.5/AIX 5.1 )
select count(*) from dstmp.stock_ims a
where exists
(select 1 from dstmp.ods_stock_ims b where a.company = b.company and a.sitinfo = b.sitinfo and a.fiscal_year = b.fiscal_year and a.period 8 = b.period )
8i
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=486 Card=1 Bytes=14) 1 0 SORT (AGGREGATE)
2 1 FILTER 3 2 INDEX (FAST FULL SCAN) OF 'STOCK_IMS_PK' (UNIQUE) (Cos t=486 Card=51086 Bytes=715204) 4 2 INDEX (RANGE SCAN) OF 'ODS_STOCK_IMS_I1' (NON-UNIQUE) (Cost=442 Card=54989 Bytes=714857)
9i
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1952 Card=1 Bytes=34 ) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (SEMI) (Cost=1952 Card=48740 Bytes=1657160) 3 2 INDEX (FAST FULL SCAN) OF 'STOCK_IMS_PK' (UNIQUE) (Cos t=811 Card=1108830 Bytes=18850110) 4 2 INDEX (FAST FULL SCAN) OF 'ODS_STOCK_IMS_I1' (NON-UNIQ UE) (Cost=173 Card=229105 Bytes=3894785)Received on Thu May 20 2004 - 01:57:24 CDT