Oracle Query Optimization with EXISTS

You can change a multitable join into a query with a sub-query using the EXISTS operator. You can only do this if the table to put into the subquery doesn’t have anything being selected from it in the SELECT statement.


OLD QUERY
----------------------------------------------------------------------------------
select   
    member_no,
    contract,
    dep,
    sbsb_ck,
    sbsb_id,
    grgr_id,
    meme_birth_dt
from
    prod_ph.xref_meme x,
    prod.cmc_mepe_prcs_elig
where  
    x.meme_ck = e.meme_ck
    and rownum = 1;


NEW OPTIMIZED QUERY WITH EXISTS
----------------------------------------------------------------------------------
select
    member_no,
    contract,
    dep,
    sbsb_ck,
    sbsb_id,
    grgr_id,
    meme_birth_dt
from
    prod_ph.xref_meme x
where
    exists(select 1 from prod.cmc_mepe_prcs_elig e where e.meme_ck = x.meme_ck)
and rownum = 1;

Get More Query Optimization Tips:
http://nimishgarg.blogspot.in/2012/10/query-optimization-tips-for-oracle.html