there is an in & exists thread on asktom. Generally speaking exists is better if the sub-query will have a larger and most costly result set than the outer query. Its the other way around for 'in'.
I may have them backward, though I think that is correct. > > From: Munish Bajaj <[EMAIL PROTECTED]> > Date: 2003/06/03 Tue AM 06:59:52 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > 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 > > > > >
|
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
|
<encoded content removed -- binaries not allowed by ListGuru>Content-Type: image/gif;
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 [EMAIL PROTECTED] for clarification.
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 [EMAIL PROTECTED] for clarification.
