title: oracle GTT Vs IN clause literals
body: <p>Have a one query , where GTT(few 100 Recs) inner joined with main table. </p>
  
  <pre><code>ex :
  insert into table1 select col2,col3 from gtt,maintable where gtt.id=maintable.id    .. few other joins 
  </code></pre>
  
  <p>The query takes around 15-20min to load to table1, but when i pass those GTT tables value hardcode in query it will completed in 4-5min. </p>
  
  <p>what could be the main difference here, is there any way i can improve performance using GTT.( cannot avoid GTT becuase sometime values may be more then 1000)</p>
  
  <p>Tried below 
  1. removed inner join and replace with IN clause ( where IN ( select id from GTT), but no luck
  2. added push_subq and unnest HINT with subquery(option 1)  , but no luck </p>
  
  <p>PLAN</p>
  
  <pre><code>
  with GTT
  
  |  74 | NESTED LOOPS                      |                           
  |  75 |  BUFFER SORT                      |                           
  |  76 |   PX RECEIVE                      |                           
  |  77 |    PX SEND BROADCAST              | :TQ20006                  
  |  78 |     SORT UNIQUE                   |                           
  |  79 |      PX RECEIVE                   |                           
  |  80 |       PX SEND HASH                | :TQ20005                  
  |  81 |        PX BLOCK ITERATOR          |                           
  |  82 |         TABLE ACCESS STORAGE FULL | STAGE_GTT    
  |  83 |  PX PARTITION HASH ITERATOR       |                           
  |  84 |   TABLE ACCESS STORAGE FULL       | EXP_TABLE                 =====&gt;103GB == cell offloaded 95%  
  
  
  
  
  With literal 
  
   129 |  PX SEND HASH                          | :TQ20006              
   130 |   HASH JOIN                            |                       
   131 |    PX PARTITION HASH INLIST            |                       
   132 |     VIEW                               |                       
   133 |      TABLE ACCESS STORAGE FULL         | EXP_TABLE       ====(27GB ) ==cell offloaded 98%
  
  
  </code></pre>