Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.vmunix.org!news-mue1.dfn.de!news-stu1.dfn.de!news.belwue.de!news.uni-stuttgart.de!carbon.eu.sun.com!btnet-feed5!btnet!news.btopenworld.com!not-for-mail From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server Subject: Re: Query Runs faster in 10G Date: Sat, 4 Mar 2006 00:44:29 +0000 (UTC) Organization: BT Openworld Lines: 52 Message-ID: References: <1141417760.979675.54930@z34g2000cwc.googlegroups.com> NNTP-Posting-Host: host86-130-246-17.range86-130.btcentralplus.com X-Trace: nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com 1141433069 3542 86.130.246.17 (4 Mar 2006 00:44:29 GMT) X-Complaints-To: news-complaints@lists.btinternet.com NNTP-Posting-Date: Sat, 4 Mar 2006 00:44:29 +0000 (UTC) X-RFC2646: Format=Flowed; Original X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 X-MSMail-Priority: Normal X-Priority: 3 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:262768 wrote in message news:1141417760.979675.54930@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