------_=_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 PROTECTED]
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:[EMAIL PROTECTED]>
<DIV><SPAN class=015043910-03062003><FONT
face="Courier New">Munish</FONT></SPAN></DIV>
<DIV><SPAN class=015043910-03062003><FONT
face="Courier New"></FONT></SPAN> </DIV>
<DIV><SPAN class=015043910-03062003><FONT face="Courier New">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.</FONT></SPAN></DIV>
<DIV><SPAN class=015043910-03062003><FONT
face="Courier New"></FONT></SPAN> </DIV>
<DIV><SPAN class=015043910-03062003><FONT
face="Courier New">Regards</FONT></SPAN></DIV>
<DIV><SPAN class=015043910-03062003><FONT face="Courier New">David
Lord</FONT></SPAN></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><FONT
face=Tahoma>-----Original Message-----<BR><B>From:</B> Munish Bajaj
[mailto:[EMAIL PROTECTED]<BR><B>Sent:</B> 03 June 2003 12:00<BR><B>To:</B>
Multiple recipients of list ORACLE-L<BR><B>Subject:</B> IN or Exists ---
performance issue<BR><BR></FONT></DIV>
<P><SPAN class=984285109-03062003>Hi Listers</SPAN></P>
<P><SPAN class=984285109-03062003>I have a unique performance problem. As a
general rule by oracle while writing SQL scripts EXISTS should be used in
place of IN. </SPAN></P>
<P><SPAN class=984285109-03062003>I'm having 2 sql for comparison using IN and
EXISTS operators.</SPAN></P><SPAN class=984285109-03062003>
<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
</SPAN><SPAN class=984285109-03062003>statement, approx 4 times and hence the
delay.</SPAN></P>
<P><SPAN class=984285109-03062003>I have a index on all the predicates
mentioned in the where clause. and the explain plan shows a index range
search.</SPAN></P>
<P><SPAN class=984285109-03062003>Can anyone please help me to reduce these
high Logical reads which result when I use the EXISTS operator.</SPAN></P>
<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></SPAN> </P></BLOCKQUOTE></SPAN></SPAN><CODE><FONT
SIZE=3><BR>
<BR>
**********************************************************************<BR>
This message (including any attachments) is confidential and may be <BR>
legally privileged. If you are not the intended recipient, you should <BR>
not disclose, copy or use any part of it - please delete all copies <BR>
immediately and notify the Hays Group Email Helpdesk at<BR>
[EMAIL PROTECTED]<BR>
Any information, statements or opinions contained in this message<BR>
(including any attachments) are given by the author. They are not <BR>
given on behalf of Hays unless subsequently confirmed by an individual<BR>
other than the author who is duly authorised to represent Hays.<BR>
<BR>
A member of the Hays plc group of companies.<BR>
Hays plc is registered in England and Wales number 2150950.<BR>
Registered Office Hays House Millmead Guildford Surrey GU2 4HJ.<BR>
**********************************************************************<BR>
</FONT></CODE>
</BODY></HTML>
------_=_NextPart_001_01C329BC.79FDEA20--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Lord, David - CSG
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
