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

From: Lord, David - CSG <david.lord_at_hays.com>
Date: Tue, 03 Jun 2003 03:44:41 -0800
Message-ID: <F001.005A8AFB.20030603034441@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C329BC.79FDEA20
Content-Type: text/plain; charset="iso-8859-1"

Munish

I've got a funny feeling that this thing about using EXISTS rather than IN is a bit of a myth. I do a lot of this sort of thing and I find that almost invariably, an IN with a simple subquery is faster than an EXISTS with a correlated subquery.

Regards
David Lord

-----Original Message-----
Sent: 03 June 2003 12:00
To: Multiple recipients of list ORACLE-L

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

This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at email.helpdesk_at_hays.com
Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays.

A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ.

------_=_NextPart_001_01C329BC.79FDEA20
Content-Type: text/html; charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE id=ridTitle>Blank</TITLE>

<STYLE>BODY {

MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 25px; COLOR: #000000; FONT-FAMILY: Arial, Helvetica }
P.msoNormal {

MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; FONT-FAMILY: Helvetica, "Times New Roman" }
LI.msoNormal {

MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; FONT-FAMILY: Helvetica, "Times New Roman" }
</STYLE>

<META content="MSHTML 6.00.2800.1106" name=GENERATOR></HEAD>
<BODY id=ridBody background=cid:984285109_at_03062003-220d>
<DIV><FONT
face="Courier New">Munish</DIV>
<DIV><FONT
face="Courier New"> </DIV>
<DIV>I've got a funny
feeling that this thing about using EXISTS rather than IN is a bit of a myth.  I do a lot of this sort of thing and I find that almost invariably, an IN with a simple subquery is faster than an EXISTS with a correlated subquery.</DIV>
<DIV><FONT
face="Courier New"> </DIV>
<DIV><FONT
face="Courier New">Regards</DIV>
<DIV>David
Lord</DIV>
<BLOCKQUOTE dir=ltr

style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"> <DIV class=OutlookMessageHeader dir=ltr align=left>-----Original Message----- From: Munish Bajaj [mailto:mbajaj_at_quark.co.in] Sent: 03 June 2003 12:00 To: Multiple recipients of list ORACLE-L Subject: IN or Exists --- performance issue </DIV> 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.

  <P><SPAN class=984285109-03062003>With IN operator</SPAN></P>
  <P>SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */</P>
  <P>COUNT(1)</P>
  <P>FROM mam_assets a</P>
  <P>WHERE 1 = 1</P>
  <P>AND a.is_current_version = 1</P>
  <P>AND a."ID" IN (SELECT dmv3.asset_id</P>
  <P>FROM mam_asset_attr_domain_values dmv3</P>
  <P>WHERE dmv3.domain_value_id = 71</P>
  <P>AND dmv3.asset_attribute_xid = 3</P>
  <P>AND dmv3.domain_xid = 7)</P>
  <P><SPAN class=984285109-03062003>With Exists Operator</SPAN></P><SPAN

class=984285109-03062003>

  <P>SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */</P>
  <P>COUNT(1)</P>
  <P>FROM mam_assets a</P>
  <P>WHERE 1 = 1</P>
  <P>AND a.is_current_version = 1</P>
  <P>AND EXISTS (SELECT dmv3.asset_id</P>
  <P>FROM mam_asset_attr_domain_values dmv3</P>
  <P>WHERE a."ID" = dmv3.asset_id</P>
  <P>AND dmv3.domain_value_id = 71</P>
  <P>AND dmv3.asset_attribute_xid = 3</P>
  <P>AND dmv3.domain_xid = 7)</P>
  <P><SPAN class=984285109-03062003>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.

  <P><SPAN class=984285109-03062003>Thanks to all</SPAN></P>
  <P><SPAN class=984285109-03062003>Best Regards</SPAN></P>
  <P><SPAN class=984285109-03062003>Munish Bajaj</SPAN></P>
  <P><SPAN

class=984285109-03062003> </BLOCKQUOTE><CODE> 
 

**********************************************************************<BR>

This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at email.helpdesk_at_hays.com 
Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. 
A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ.

**********************************************************************<BR>

</CODE>
</BODY></HTML>

------_=_NextPart_001_01C329BC.79FDEA20--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Lord, David - CSG
  INET: david.lord_at_hays.com

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:44:41 CDT