Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!z34g2000cwc.googlegroups.com!not-for-mail
From: ronnie_yours@yahoo.com
Newsgroups: comp.databases.oracle.server
Subject: Query Runs faster in 10G
Date: 3 Mar 2006 12:29:21 -0800
Organization: http://groups.google.com
Lines: 60
Message-ID: <1141417760.979675.54930@z34g2000cwc.googlegroups.com>
NNTP-Posting-Host: 64.201.8.130
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1141417766 6819 127.0.0.1 (3 Mar 2006 20:29:26 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 3 Mar 2006 20:29:26 +0000 (UTC)
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: z34g2000cwc.googlegroups.com; posting-host=64.201.8.130;
   posting-account=tX4fGwwAAAA7jNMfYzgk596bkdoCBp9f
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:262731

Hi,

I have a query which takes 0.2 seconds in 10G but takes 18 seconds in
8i.

the query is

 SELECT n.story_id, n.date_of_story AS sdate, n.headline,
lp.product_location_description
        FROM news_story n, int_news_locations l, flat_regionhierarchy
r, lkp_product_location_name lp
       WHERE r.parent_region_id = 50015
         AND n.product_location_id between 500 and 599
         AND l.region_id = r.child_region_id
         AND lp.product_location_id(+) = n.product_location_id
         AND n.story_id = l.story_id;

EXPLAIN PLAN IN 10G
==================

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

EXPLAIN PLAN IN 8I
================

SELECT STATEMENT, GOAL =
CHOOSE			Cost=4751	Cardinality=3872	Bytes=464640
 HASH JOIN OUTER			Cost=4751	Cardinality=3872	Bytes=464640
  HASH JOIN			Cost=4745	Cardinality=3872	Bytes=329120
   NESTED LOOPS			Cost=41	Cardinality=16500	Bytes=264000
    TABLE ACCESS FULL	Object owner=CODB	Object
name=INT_NEWS_LOCATIONS	Cost=41	Cardinality=80930	Bytes=647440
    INDEX UNIQUE SCAN	Object owner=CODB	Object
name=PK_REGION_CHILDPARENTID		Cardinality=63	Bytes=504
   TABLE ACCESS FULL	Object owner=CODB	Object
name=NEWS_STORY	Cost=4679	Cardinality=17983	Bytes=1240827
  TABLE ACCESS FULL	Object owner=CODB	Object
name=LKP_PRODUCT_LOCATION_NAME	Cost=1	Cardinality=53	Bytes=1855


Why is it behaving differently in 10G and how should i improve
performance in 8i.

Thanks
Ron