RE: IN or Exists --- performance issue

[Kevin Toepke]

I nearly always have issues with blanket statements on performance. That includes the statement that EXISTS should be used in place of IN.   If the IN performs better in the particular case, use it. If it doesn't perfom adiquately then, by all means, convert it to an EXISTS. And do the reverse as well.   I write SQL to answer the given question. If the question is stated "xxxx is in yyyy" then I code it using an IN. If the question is stated "process the zzzz that have qqqq" then I code it using an EXISTS. If the query doesn't perform well, then I convert it to the other subquery type.   Especially in later versions of O8i and later Oracle will frequently auto-convert the subquery for you. I've seen it convert both ways.   Kevin -----Original Message----- From: Munish Bajaj [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 7:00 AM To: Multiple recipients of list ORACLE-L Subject: IN or Exists --- performance issue 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