Oracle-L: Re: IN or Exists --- performance issue

From: <rgaffuri_at_cox.net>
Date: Tue, 03 Jun 2003 03:39:43 -0800
Message-ID: <F001.005A8AE1.20030603033943@fatcity.com>

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 <mbajaj_at_quark.co.in>
> Date: 2003/06/03 Tue AM 06:59:52 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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
>
>
>
>
>

Blank

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 Postmaster_at_fatcity.com for clarification.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.net

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).

Received on Tue Jun 03 2003 - 06:39:43 CDT