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

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Tue, 03 Jun 2003 03:59:40 -0800
Message-ID: <F001.005A8B30.20030603035940@fatcity.com>

Munish,

You are right, as a *general rule of thumb* EXISTS is *usually* faster than IN..

There can however be problems when an EXISTS is used to manipulate or select data from a very large table, where the row exists in a far smaller table, as it will read every row in the large table, and then scan the smaller table for corresponding rows.. Is this the case with you?

You have a few options:

Use IN ;)
Try an ALWAYS_SJ(MERGE) hint.
Set the ALWAYS_SEMI_JOIN init.ora parameter to "MERGE" (though this has had Ora-600 problems reported against it - so test it thoroughly first!).

HTH Mark

 Mark Leith             | T: +44 (0)1905 330 281
 Sales & Marketing      | F: +44 (0)870 127 5283
 Cool Tools UK Ltd      | E: mark_at_cool-tools.co.uk
===================================================
           http://www.cool-tools.co.uk
       Maximising throughput & performance

-----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
ALWAYS_SEMI_JOIN
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

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003



-- 

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


-- 

Author: Mark Leith
  INET: mark_at_cool-tools.co.uk

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:59:40 CDT