Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query Runs faster in 10G

Re: Query Runs faster in 10G

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 4 Mar 2006 00:44:29 +0000 (UTC)
Message-ID: <duantd$3em$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com>

<ronnie_yours_at_yahoo.com> wrote in message news:1141417760.979675.54930_at_z34g2000cwc.googlegroups.com...
> Hi,
>
> I have a query which takes 0.2 seconds in 10G but takes 18 seconds in
> 8i.
>
> SELECT STATEMENT, GOAL =
> ALL_ROWS Cost=6703 Cardinality=40726 Bytes=5335106
> HASH JOIN Cost=6703 Cardinality=40726 Bytes=5335106
> INDEX RANGE SCAN Object owner=CODB Object
> name=PK_REGION_CHILDPARENTID Cost=4 Cardinality=788 Bytes=7880
> HASH JOIN RIGHT OUTER Cost=6698 Cardinality=33077 Bytes=4002317
> MAT_VIEW ACCESS BY INDEX ROWID Object owner=CODB Object
> name=LKP_PRODUCT_LOCATION_NAME Cost=2 Cardinality=13 Bytes=481
> INDEX RANGE SCAN Object owner=CODB Object
> name=SYS_C006198 Cost=1 Cardinality=13
> HASH JOIN Cost=6695 Cardinality=33077 Bytes=2778468
> MAT_VIEW ACCESS FULL Object owner=CODB Object
> name=INT_NEWS_LOCATIONS Cost=112 Cardinality=80624 Bytes=806240
> MAT_VIEW ACCESS FULL Object owner=CODB Object
> name=NEWS_STORY Cost=6364 Cardinality=32557 Bytes=2409218
>
>

Without looking at all the details, the first thing that stands out is the

    HASH JOIN RIGHT OUTER
in the 10g plan.

This is something that 8i cannot do - the table order for outer joins is constrained so that the preserved table appears earlier than the extended table until 10g where hash outer joins can be operated 'the wrong way round'. It is quite likely that most of your performance benefit comes from this change.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

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

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Received on Fri Mar 03 2006 - 18:44:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US