Re: Query Runs faster in 10G

As Jonathan says, 10g plan is benefitting from RIGHT OUTER HASH JOIN. Thus if you have a situation like,

"AND VERY_LARGE_TABLE.COLUMN= VERY_SMALL_TABLE.COLUMN(+)" in your WHERE clause, Oracle 10g, often, does scan the VERY_SMALL_TABLE first and then HASH JOIN it with the VERY_LARGE_TABLE.

>From your TKPROF data, it seems on 8i most of the time difference is
due to delta in ELAPSED_TIME and CPU_TIME (about 15 secs. on 8i; about -4 sec on 10g). This is probably explained by the high amount of DISK I/O that 8i has to do (47019) for full-scanning INT_NEWS_LOCATION.

  80954 TABLE ACCESS FULL INT_NEWS_LOCATIONS (8i)

On 10g, you do not directly full-scan the INT_NEWS_LOCATION, rather the matl. view on that.

80028 MAT_VIEW ACCESS FULL INT_NEWS_LOCATIONS (cr=500 pr=496 pw=0 time=161678 us) (10g)

What are the indexes on INT_NEWS_LOCATIONS? Looks like you may have a composite index on CHILD_REGION_ID and PARENT_REGION_ID. Do you have index on STORY_ID on both the tables?

What happens you rewrite the query as -

SELECT n.story_id, n.date_of_story AS sdate, n.headline, (select lp.product_location_description

from lkp_product_location_name lp
where lp.product_location_id(+) = product_location_id)
product_location_description
    FROM news_story n, int_news_locations l, flat_regionhierarchy r
       WHERE r.parent_region_id = 50015
         AND l.region_id = r.child_region_id
         AND n.story_id = l.story_id
         AND n.product_location_id >= 500
         AND n.product_location_id < 600;