For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
select * from DEPT where NOT EXISTS (select * from EMP where EMP.deptno = DEPT.deptno) /
select from T1 a,T3 c where a.C1=c.C1 and Not EXISTS (select 1 from T2 WHERE T2.C1 = a.C1);
select from T1 a,T3 c where a.C1=c.C1 and Not EXISTS (select 1 from T2 WHERE T2.C1 = a.C1 or T2.C1 is null);
Changed my actual query with NOT EXISTS,Still performance lacks , Any idea to improve? select /*+ PUSH_SUBQ */ from T1 a,T3 c where a.C1=c.C1 and a.C2=c.C3 and trunc(Update_date)>='19-APR-2009' and trunc(Update_date) < sysdate and c.C3 ='P' and Not EXISTS (select 1 from T2 WHERE T2.C1 = a.C1); There is a PK on a.C1,T2.C1 index on c.C3
Changed my actual query with NOT EXISTS,Still performance lacks , Any idea to improve?
select * from T1 a JOIN T3 c on a.C1=c.C1 and a.C2=c.C3 LEFT JOIN T2 on T2.C1 = a.C1 where Update_date >= to_date('19-04-2009','DD-MM-YYYY') and Update_date < sysdate and c.C3 ='P' and t2.c1 is null;
SELECT STATEMENT | | 507K| 56M| | 16M (3)| 40:02:33 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10005 | 507K| 56M| | 16M (3)| 40:02:33 | | | Q1,05 | P->S | QC (RAND) | | 3 | HASH UNIQUE | | 507K| 56M| 124M| 16M (3)| 40:02:33 | | | Q1,05 | PCWP | | | 4 | PX RECEIVE | | 507K| 56M| | 16M (3)| 40:02:32 | | | Q1,05 | PCWP | | | 5 | PX SEND HASH | :TQ10004 | 507K| 56M| | 16M (3)| 40:02:32 | | | Q1,04 | P->P | HASH | |* 6 | HASH JOIN OUTER BUFFERED | | 507K| 56M| | 16M (3)| 40:02:32 | | | Q1,04 | PCWP | | | 7 | PX RECEIVE | | 507K| 47M| | 16M (2)| 39:58:18 | | | Q1,04 | PCWP | | | 8 | PX SEND HASH | :TQ10002 | 507K| 47M| | 16M (2)| 39:58:18 | | | Q1,02 | P->P | HASH | | 9 | NESTED LOOPS | | 507K| 47M| | 16M (2)| 39:58:18 | | | Q1,02 | PCWP | | |* 10 | HASH JOIN RIGHT ANTI | | 2549K| 192M| 423M| 14M (3)| 36:26:24 | | | Q1,02 | PCWP | | | 11 | PX RECEIVE | | 93M| 624M| | 36738 (10)| 00:05:28 | | | Q1,02 | PCWP | | | 12 | PX SEND HASH | :TQ10001 | 93M| 624M| | 36738 (10)| 00:05:28 | | | Q1,01 | P->P | HASH | | 13 | PX PARTITION HASH ALL | | 93M| 624M| | 36738 (10)| 00:05:28 | 1 | 12 | Q1,01 | PCWC | | | 14 | INDEX FULL SCAN | PK_T2 | 93M| 624M| | 36738 (10)| 00:05:28 | 1 | 12 | Q1,01 | PCWP | | | 15 | BUFFER SORT | | | | | | | | | Q1,02 | PCWC | | | 16 | PX RECEIVE | | 79M| 5456M| | 14M (3)| 36:16:56 | | | Q1,02 | PCWP | | | 17 | PX SEND HASH | :TQ10000 | 79M| 5456M| | 14M (3)| 36:16:56 | | | | S->P | HASH | | 18 | PARTITION RANGE ALL | | 79M| 5456M| | 14M (3)| 36:16:56 | 1 | 20 | | | | |* 19 | TABLE ACCESS BY LOCAL INDEX ROWID| D_T1 | 79M| 5456M| | 14M (3)| 36:16:56 | 1 | 20 | | | | | 20 | INDEX FULL SCAN | IN_T1 | 189M| | | 280K (9)| 00:41:41 | 1 | 20 | | | | | 21 | PARTITION RANGE ITERATOR | | 1 | 20 | | 2 (0)| 00:00:01 | KEY | KEY | Q1,02 | PCWP | | |* 22 | TABLE ACCESS BY LOCAL INDEX ROWID | T3 | 1 | 20 | | 2 (0)| 00:00:01 | KEY | KEY | Q1,02 | PCWP | | |* 23 | INDEX UNIQUE SCAN | PK_T3 | 1 | | | 1 (0)| 00:00:01 | KEY | KEY | Q1,02 | PCWP | | | 24 | PX RECEIVE | | 65M| 1129M| | 27512 (31)| 00:04:06 | | | Q1,04 | PCWP | | | 25 | PX SEND HASH | :TQ10003 | 65M| 1129M| | 27512 (31)| 00:04:06 | | | Q1,03 | P->P | HASH | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------