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  

Content-Type: text/plain; name="ReadMe.txt"; charset="us-ascii" Content-Transfer-Encoding: 7bit

The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail.

This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact Postmaster_at_fatcity.com for clarification.

------_=_NextPart_000_01C329B5.FB34D970--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Munish Bajaj
  INET: mbajaj_at_quark.co.in

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).