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:
1) Use IN ;)
2) Try an ALWAYS_SJ(MERGE) hint.
3) 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: [EMAIL PROTECTED]
===================================================
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: [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).
