Re: Request in 9i slower than in 8i ...

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 20 May 2004 08:18:03 +0000 (UTC)
Message-ID: <c8hpjr$qdb$1@titan.btinternet.com>

Oracle 9 automatically unnests subqueries whenever it is legal to do so. This is why you see the hash semi-join in the second plan.

To stop this happening for a specific query, you can put in the NO_UNNEST hint into
the subquery.

Oracle 10g will cost both options are select the cheaper.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"zeb" <spam_at_nowhere.com> wrote in message
news:40ac5592$0$14063$626a14ce_at_news.free.fr...


>

> 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 - 03:18:03 CDT