title: same SQL but different explain plan
body: <p>I am running same SQL (below), at 2 different environments with same index set and table size.
  But they gave me 2 different explain plans  (<a href="https://docs.google.com/document/d/1AY79pDJY4Wbsg8Iu2EtZ6kgxnrkAsyfx9HIarWdUj38/edit?hl=en" rel="nofollow">attached</a>)</p>
  
  <ol>
  <li>uses a Merge Join Cartesian -- very slow</li>
  <li>uses PX Coordinator / PX Send / PX RECEIVE -- very fast</li>
  </ol>
  
  <p>Query:</p>
  
  <pre><code>SELECT *
  FROM SIEBEL.S_PARTY PRTY, SIEBEL.S_CONTACT CONT, HPQ_IF_ENTERPRISE_DIRECTORY ED,SIEBEL.S_BU BU 
  WHERE PRTY.ROW_ID = CONT.PAR_ROW_ID 
  AND BU.ROW_ID(+)=CONT.BU_ID 
  AND CONT.EMP_NUM IS NOT NULL 
  AND ED.HPSTATUS NOT IN ('Terminated', 'Retired', 'Deceased') 
  AND ED.EMPLOYEENUMBER = UPPER (LPAD (CONT.EMP_NUM, 8, '0')) 
  AND (SUBSTR(ED.MODIFYTIMESTAMP,1,14) &gt;= '19800101' OR ED.MODIFYTIMESTAMP IS NULL)
  </code></pre>
  
  <p>Any idea what is the possible things to cause this difference? And what does 2nd explain plan with (PX things) mean?</p>
  
  <p>Note that I am not looking for changing the SQL query (freeze in production).</p>
  
  <p>Thanks a lot.</p>