title: Oracle Query Tuning
body: <p>Does anyone know why that, of these 3 querys that I have, the fastest one is the one that uses <code>where not exists</code>?.</p>
  
  <p>This article <a href="http://www.dba-oracle.com/oracle_tips_subq_rewrite.htm" rel="nofollow">Rewrite SQL Subqueries as Outer Joins</a> says that you can change a <code>where not exists</code> to a normal <code>join</code>. Did I misunderstand? Can someone explain the difference?</p>
  
  <p>This one does it in .73 seconds fetches all 3293 rows</p>
  
  <pre><code>SELECT *
  FROM
  (SELECT TRIM(TO_CHAR(C21.PINO,'999999999999999')) DN,
  C21.INVD,
  TRIM(TO_CHAR(C21.ORNO,'999999999999999')),
  C21.CFRW,
  C21.LIFEX,
  C22.PONO,
  C22.ITEM,
  C22.OQUA,
  C71.REFA,
  C71.CDEC,
  C81.COPO,
  DECODE(SUBSTR(TRIM(C22.ITEM),1,3), 'PRD', 1, 'ASY', 2, 'SLA', 3),
  C84.NAMA,
  C84.CCTY
  FROM CMS.CMPPS021@RO_CMS_PRD C21,
  CMS.CMPPS022@RO_CMS_PRD C22,
  CMS.CMPPS071@RO_CMS_PRD C71,
  CMS.CMPPS081@RO_CMS_PRD C81,
  CMS.CMPPS084@RO_CMS_PRD C84
  WHERE C21.PINO = C22.PINO
  AND DECODE( SUBSTR(TRIM(C22.ITEM),1,3) , 'PRD', 'Y', 'ASY', 'Y', 'SLA', 'Y', 'N' ) = 'Y'
  AND C21.ORNO = C71.ORNO
  AND C21.ORNO = C81.ORNO
  AND C22.PONO = C81.PONO
  AND C21.ORNO = C84.ORNO
  AND C84.CTYP = 'ST'
  AND C21.NCMP = 'F555'
  AND C21.INVD &gt;= TO_DATE('01012011','MMDDYYYY')
  ORDER BY C21.PINO,
  C22.PONO
  ) T1
  WHERE NOT EXISTS
  ( SELECT 1 FROM SHIPPINGCONTROL S WHERE T1.DN=S.S_DN
  ) ; 
  </code></pre>
  
  <p>This one does it in 2 seconds</p>
  
  <pre><code>SELECT TRIM(TO_CHAR(C21.PINO,'999999999999999')) DN,
  C21.INVD,
  TRIM(TO_CHAR(C21.ORNO,'999999999999999')),
  C21.CFRW,
  C21.LIFEX,
  C22.PONO,
  C22.ITEM,
  C22.OQUA,
  C71.REFA,
  C71.CDEC,
  C81.COPO,
  DECODE(SUBSTR(TRIM(C22.ITEM),1,3),'PRD',1,'ASY',2,'SLA',3),
  C84.NAMA,
  C84.CCTY
  FROM CMS.CMPPS021@RO_CMS_PRD C21,
  CMS.CMPPS022@RO_CMS_PRD C22,
  CMS.CMPPS071@RO_CMS_PRD C71,
  CMS.CMPPS081@RO_CMS_PRD C81,
  CMS.CMPPS084@RO_CMS_PRD C84,
  (SELECT C21.PINO DN
  FROM CMS.CMPPS021@RO_CMS_PRD C21
  WHERE C21.INVD&gt;=TO_DATE('01012011','MMDDYYYY')
  AND C21.NCMP     ='F555'
  MINUS
  SELECT TO_NUMBER(G_DN) FROM GENERALINFO
  ) DNSTOFIND
  WHERE C21.PINO =C22.PINO
  AND DECODE(SUBSTR(TRIM(C22.ITEM),1,3),'PRD','Y','ASY','Y','SLA','Y','N')='Y'
  AND C21.ORNO =C71.ORNO
  AND C21.ORNO =C81.ORNO
  AND C22.PONO =C81.PONO
  AND C21.ORNO =C84.ORNO
  AND C84.CTYP ='ST'
  AND DNSTOFIND.DN =C21.PINO
  ORDER BY C21.PINO,
  C22.PONO;
  </code></pre>
  
  <p>This one does it in 4 seconds</p>
  
  <pre><code>SELECT TRIM(TO_CHAR(C21.PINO,'999999999999999')) DN,
  C21.INVD,
  TRIM(TO_CHAR(C21.ORNO,'999999999999999')),
  C21.CFRW,
  C21.LIFEX,
  C22.PONO,
  C22.ITEM,
  C22.OQUA,
  C71.REFA,
  C71.CDEC,
  C81.COPO,
  DECODE(SUBSTR(TRIM(C22.ITEM),1,3),'PRD',1,'ASY',2,'SLA',3),
  C84.NAMA,
  C84.CCTY
  FROM CMS.CMPPS021@RO_CMS_PRD C21,
  CMS.CMPPS022@RO_CMS_PRD C22,
  CMS.CMPPS071@RO_CMS_PRD C71,
  CMS.CMPPS081@RO_CMS_PRD C81,
  CMS.CMPPS084@RO_CMS_PRD C84,
  (SELECT C21.PINO DN
  FROM CMS.CMPPS021@RO_CMS_PRD C21,
      GENERALINFO G
  WHERE C21.INVD&gt;=TO_DATE('01012011','MMDDYYYY')
  AND C21.NCMP     ='F555'
  AND C21.PINO     =G.G_DN(+)
  AND G.G_DN        IS NULL
  ) DNSTOFIND
  WHERE C21.PINO =C22.PINO
  AND DECODE(SUBSTR(TRIM(C22.ITEM),1,3),'PRD','Y','ASY','Y','SLA','Y','N')='Y'
  AND C21.ORNO =C71.ORNO
  AND C21.ORNO =C81.ORNO
  AND C22.PONO =C81.PONO
  AND C21.ORNO =C84.ORNO
  AND C84.CTYP ='ST'
  AND DNSTOFIND.DN =C21.PINO
  ORDER BY C21.PINO,
  C22.PONO;
  </code></pre>