title: Oracle Sub-select taking a long time
body: <p>I have a SQL Query that comprise of two level sub-select. This is taking too much time. </p>
  
  <p>The Query goes like:</p>
  
  <pre><code>   select * from DALDBO.V_COUNTRY_DERIV_SUMMARY_XREF
      where calculation_context_key = 130205268077
      and DERIV_POSITION_KEY in 
            (select ctry_risk_derivs_psn_key 
              from DALDBO.V_COUNTRY_DERIV_PSN
              where calculation_context_key = 130111216755 
              --and ctry_risk_derivs_psn_key = 76296412
              and CREDIT_PRODUCT_TYPE = 'SWP OP'
              and CALC_OBLIGOR_COUNTRY_OF_ASSETS in 
                  (select ctry_cd 
                   from DALDBO.V_PSN_COUNTRY
                   where calculation_context_key = 130134216755 
                  --and ctry_risk_derivs_psn_key = 76296412
                  )
             )
  </code></pre>
  
  <p>These tables are huge! Is there any optimizations available?</p>