Skip to Main Content

SQL & PL/SQL

Announcement

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.

NOT EXISTS

767585Aug 17 2010 — edited Aug 17 2010
Any body can help me to write USING Not EXISTS ?

select
from T1 a,T3 c
where a.C1=c.C1
and a.C1 Not in (select T2.C1 from T2)

Comments

Lokanath Giri
To find all dept not existing in EMP table
 select *
 from DEPT
 where NOT EXISTS
   (select *
     from EMP
     where EMP.deptno = DEPT.deptno)
 /
737905
Try this:
select 
from T1 a,T3 c
where a.C1=c.C1
and Not EXISTS (select 1 from T2 WHERE T2.C1 = a.C1);
Aketi Jyuuzou
At not in condition,
Sometimes we must think whether there is NULL or not :-)
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);
767585
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
Edited by: user13006393 on Aug 17, 2010 6:57 AM
SomeoneElse
Please read these:

When your query takes too long
503834

How to Post a SQL statement tuning request
865295
Sven W.
user13006393 wrote:
Changed my actual query with NOT EXISTS,Still performance lacks , Any idea to improve?
Maybe like this
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;
767585
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                 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
791732
Assuming third table is t2...


SELECT * FROM T1 a, T3 c
where a.c1 = c.c1
and not exists (select b.c1 from t2 b where b.c1 = a.c1)
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 14 2010
Added on Aug 17 2010
8 comments
1,076 views