Query Runs faster in 10G

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 Received on Fri Mar 03 2006 - 14:29:21 CST