Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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