Avoid using sub-selects and redundant expressions | Other important SQL tuning hints

IN versus Exists

To quote the Oracle Tuning Reference, "In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS."

IN and EXISTS work very differently. In case of IN, the subquery is executed first and then the result is compared with the outer table. The EXISTS clause, however, is evaluated for every value in the outer table. Here is an example for WebSphere Commerce that shows that EXISTS performs much better than IN when the selective predicate is the parent query. The query below was written using IN, and it was running for a long time. According the above role, the query is a good candidate to use EXISTS instead of IN. The selective predicates (T2.REGISTERTYPE IN ('R') AND mr1.ROLE_ID = -29) are in the parent query.

SELECT DISTINCT t1.state, t1.member_id, t1.optcounter, t1.type, 
t2.field2, t2.registrationupdate, t2.field3, t2.lastorder, 
t2.language_id, t2.prevlastsession, t2.setccurr, t2.dn, 
t2.registrationcancel, t2.lastsession, t2.registration, t2.field1, 
t2.registertype, t2.profiletype 
FROM userreg, mbrrole mr1 , users t2, member t1
WHERE             
        t2.registertype IN ('R') 
        AND mr1.role_id = -29 
        AND (EXISTS
              ( SELECT 1 
                FROM mbrrel mr, mbrrole ml 
                WHERE (
                         (mr.descendant_id = -1000 
                             AND mr.sequence=1 
                             AND mr1.orgentity_id = mr.ancestor_id) 
                         OR 
                         (ml.role_id IN (-1,-20,-27) 
                             AND ml.member_id = -1000 
                             AND mr1.orgentity_id = ml.orgentity_id) 
             OR 
             ( 
                             (ml.role_id IN (-1,-20,-27) 
                                 AND ml.member_id = -1000 
                                 AND mr.ancestor_id = ml.orgentity_id ) 
                             AND mr1.orgentity_id = mr.descendant_id)
                         )
                ) 
                OR mr1.orgentity_id IN
                   (SELECT ancestor_id 
                    FROM mbrrel mrl, mbrrole mble 
                    WHERE mble.member_id=-1000
                          AND mble.role_id IN  (-1,-20,-27) 
                          AND mrl.descendant_id = mble.orgentity_id
                   ) 
        )
        AND t2.users_id = mr1.member_id 
        AND userreg.users_id = t2.users_id 
        AND t1.member_id = t2.users_id 
        AND t1.type = 'u'