|
Hi Listers I have a unique performance problem. As a general rule by oracle while writing SQL scripts EXISTS should be used in place of IN. I'm having 2 sql for comparison using IN and EXISTS operators. With IN operator SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */ COUNT(1) FROM mam_assets a WHERE 1 = 1 AND a.is_current_version = 1 AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) With Exists Operator SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */ COUNT(1) FROM mam_assets a WHERE 1 = 1 AND a.is_current_version = 1 AND EXISTS (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE a."ID" = dmv3.asset_id AND dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) The Statement having exists is taking more time than the one with IN operator. IN operator statement time = 3sec and the Exists operator statement time = 12 sec. After analysis I have come to know that the EXISTS statement is causing more logical block reads that IN statement, approx 4 times and hence the delay. I have a index on all the predicates mentioned in the where clause. and the explain plan shows a index range search. Can anyone please help me to reduce these high Logical reads which result when I use the EXISTS operator. Thanks to all Best Regards Munish Bajaj
|
<<Blank Bkgrd.gif>>
