title: How can I optimize this query over 15000 records for Datawarehouse
body: <p>Can someone help me to optimize this query?</p>
  
  <p>This is taking a lot of time to get done in TOAD:</p>
  
  <pre class="lang-sql prettyprint-override"><code>select dim.comply_hotlist_sid --,DIM.HOTLISTID,dim.tin1,dim.* 
  from EDW_WDS.D_T2_COMPLY_HOT_LIST dim 
  where dim.COMPLY_HOTLIST_CURRENT_FLG = 'Y' 
  and DIM.HOTLISTID in  
  ( 
     select distinct CMP.HOTLISTID 
     from MKT.D_CUSTOMER tp 
     join DW_ods.N_COMPLY_HOT_LIST cmp 
       on CMP.TIN1 = TP.SSN_TAX_ID 
     join  DW_DW.D_LOAN_VERSION_2 ln 
       on LN.LOAN_ACCT_NBR = TP.LOAN_ACCT_NBR 
     join DW_DW.AGG_LOAN_SS_MONTHLY mnt 
       on MNT.LOAN_SID= LN.LOAN_SID 
     where CMP.STATUSCODE = 'A' 
     and CMP.ISLASTLOAD = 'Y' 
     and TP.LOAN_ACCT_NBR is not null 
     and TP.SSN_TAX_ID &lt;&gt; ' ' 
     and TP.SSN_TAX_ID is not null )
  </code></pre>