title: What is an optimized way of joining large tables in Spark SQL
body: <p>I have a need of joining tables using Spark SQL or Dataframe API. Need to know what would be optimized way of achieving it.</p>
  
  <p>Scenario is:</p>
  
  <ol>
  <li>All data is present in Hive in ORC format (Base Dataframe and Reference files).</li>
  <li>I need to join one Base file (Dataframe) read from Hive with 11-13 other reference file to create a big in-memory structure (400 columns) (around 1 TB in size)</li>
  </ol>
  
  <p>What can be best approach to achieve this? Please share your experience if some one has encounter similar problem.</p>