Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: IN or Exists --- performance issue

RE: IN or Exists --- performance issue

From: Kevin Toepke <ktoepke_at_rlcarriers.com>
Date: Tue, 03 Jun 2003 05:05:09 -0800
Message-ID: <F001.005A8C0F.20030603050509@fatcity.com>


I nearly always have issues with blanket statements on performance. That includes the statement that EXISTS should be used in place of IN.  

If the IN performs better in the particular case, use it. If it doesn't perfom adiquately then, by all means, convert it to an EXISTS. And do the reverse as well.  

I write SQL to answer the given question. If the question is stated "xxxx is in yyyy" then I code it using an IN. If the question is stated "process the zzzz that have qqqq" then I code it using an EXISTS. If the query doesn't perform well, then I convert it to the other subquery type.  

Especially in later versions of O8i and later Oracle will frequently auto-convert the subquery for you. I've seen it convert both ways.  

Kevin

-----Original Message-----
Sent: Tuesday, June 03, 2003 7:00 AM
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  

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Kevin Toepke
  INET: ktoepke_at_rlcarriers.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 - 08:05:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US