Oracle Query Tuning

Does anyone know why that, of these 3 querys that I have, the fastest one is the one that uses where not exists?.

This article Rewrite SQL Subqueries as Outer Joins says that you can change a where not exists to a normal join. Did I misunderstand? Can someone explain the difference?

This one does it in .73 seconds fetches all 3293 rows

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开发者_运维知识库 >= 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
) ; 

This one does it in 2 seconds

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>=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;

This one does it in 4 seconds

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>=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;

---

I notice that you were doing a lot of function based calculations on the where clause, column indexes are surpressed where functions are used, unless the function is index based,

---

"Does anyone know why that, of these 3 querys that I have, the fastest one is the one that uses where not exists?"

It's the only one which references the SHIPPINGCONTROL table, so it's a different query. One would expect different queries to have different execution plans and hence different retrieval times.