title: Hive Map Join. Hive picking the bigger table to store in cache
body: <p>I have the below properties set.</p>
  
  <pre><code>set hive.auto.convert.join=true;
  set hive.optimize.ppd=true;
  </code></pre>
  
  <p>Table A has 25 Million records. Table B has 44 million records. But there exists conditions on where clause which has filters on Table B. So, after applying the filters the number of records come down to 2 million.</p>
  
  <p>Instead of processing the map join for table B, HIVE chooses table A. 25 million records are cached into all the data nodes.</p>
  
  <p>Below is the query used</p>
  
  <pre><code>select col1,col2,col3,col4 
      from table_A a 
      join
     table_B c
      on
      a.account_number=c.account_number and c.ins_date between '$date_6' and '$date_cur'.
  </code></pre>
  
  <p>What should be done to make sure HIVE caches table B?</p>
  
  <blockquote>
    <p>Plan after including the Stream table hint on the larger table-</p>
    
    <p><strong>STAGE DEPENDENCIES:
      Stage-4 is a root stage
      Stage-3 depends on stages: Stage-4
      Stage-0 depends on stages: Stage-3
    STAGE PLANS:
      Stage: Stage-4
        Map Reduce Local Work
          Alias -> Map Local Tables:
            b
              Fetch Operator
                limit: -1
          Alias -> Map Local Operator Tree:
            b
              TableScan
                alias: b
                Statistics: Num rows: 23894045 Data size: 7048743275 Basic stats: COMPLETE Column stats: NONE
                HashTable Sink Operator
                  condition expressions:
                    0 {cm_mac_fin} {wan_mac} {restart} {reboot} {day_id}
                    1 {division} {region}
                  keys:
                    0 cm_mac_fin (type: string)
                    1 mac (type: string)
      Stage: Stage-3
        Map Reduce
          Map Operator Tree:
              TableScan
                alias: a
                Statistics: Num rows: 2599797 Data size: 678547017 Basic stats: COMPLETE Column stats: NONE
                Map Join Operator
                  condition map:
                       Left Outer Join0 to 1
                  condition expressions:
                    0 {cm_mac_fin} {wan_mac} {restart} {reboot} {day_id}
                    1 {mac} {division} {region}
                  keys:
                    0 cm_mac_fin (type: string)
                    1 mac (type: string)
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col8, _col9, _col10
                  Statistics: Num rows: 26283450 Data size: 7753617770 Basic stats: COMPLETE Column stats: NONE
                  Select Operator
                    expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int), _col3 (type: int), _col4 (type: date), _col8 (type: string), _col9 (type: string), _col10 (type: string)
                    outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7
                    Statistics: Num rows: 26283450 Data size: 7753617770 Basic stats: COMPLETE Column stats: NONE
                    File Output Operator
                      compressed: false
                      Statistics: Num rows: 26283450 Data size: 7753617770 Basic stats: COMPLETE Column stats: NONE
                      table:
                          input format: org.apache.hadoop.mapred.TextInputFormat
                          output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                          serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
          Local Work:
            Map Reduce Local Work
          Execution mode: vectorized
      Stage: Stage-0
        Fetch Operator
          limit: -1
          Processor Tree:
            ListSink</strong></p>
    
    <p>Plan after including the map join hint on the smaller table- </p>
    
    <p><strong>STAGE DEPENDENCIES:
      Stage-4 is a root stage
      Stage-3 depends on stages: Stage-4
      Stage-0 depends on stages: Stage-3
    STAGE PLANS:
      Stage: Stage-4
        Map Reduce Local Work
          Alias -> Map Local Tables:
            b
              Fetch Operator
                limit: -1
          Alias -> Map Local Operator Tree:
            b
              TableScan
                alias: b
                Statistics: Num rows: 23894045 Data size: 7048743275 Basic stats: COMPLETE Column stats: NONE
                HashTable Sink Operator
                  condition expressions:
                    0 {cm_mac_fin} {wan_mac} {restart} {reboot} {day_id}
                    1 {division} {region}
                  keys:
                    0 cm_mac_fin (type: string)
                    1 mac (type: string)
      Stage: Stage-3
        Map Reduce
          Map Operator Tree:
              TableScan
                alias: a
                Statistics: Num rows: 2599797 Data size: 678547017 Basic stats: COMPLETE Column stats: NONE
                Map Join Operator
                  condition map:
                       Left Outer Join0 to 1
                  condition expressions:
                    0 {cm_mac_fin} {wan_mac} {restart} {reboot} {day_id}
                    1 {mac} {division} {region}
                  keys:
                    0 cm_mac_fin (type: string)
                    1 mac (type: string)
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col8, _col9, _col10
                  Statistics: Num rows: 26283450 Data size: 7753617770 Basic stats: COMPLETE Column stats: NONE
                  Select Operator
                    expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int), _col3 (type: int), _col4 (type: date), _col8 (type: string), _col9 (type: string), _col10 (type: string)
                    outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7
                    Statistics: Num rows: 26283450 Data size: 7753617770 Basic stats: COMPLETE Column stats: NONE
                    File Output Operator
                      compressed: false
                      Statistics: Num rows: 26283450 Data size: 7753617770 Basic stats: COMPLETE Column stats: NONE
                      table:
                          input format: org.apache.hadoop.mapred.TextInputFormat
                          output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                          serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
          Local Work:
            Map Reduce Local Work
          Execution mode: vectorized
      Stage: Stage-0
        Fetch Operator
          limit: -1
          Processor Tree:
            ListSink</strong></p>
  </blockquote>