yogi@mydb.mu>set autotrace traceonly
yogi@mydb.mu>  select *
  2  from tableA
  3  where (tableA.doc_num,tableA.doc_type) not in (select  tableB.doc_num,tableB.doc_type from tableB union all select  tableC.doc_num,tableC.doc_type from tableC )
  4* and (tableA.doc_num,tableA.doc_type)  in (select tableD.doc_num,tableD.doc_type from tableD)

Elapsed: 00:00:00.14

Execution Plan
__________________________________________________________
Plan hash value: 236380764

------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     1 |    10 |    13   (8)| 00:00:01 |
|*  1 |  FILTER             |        |       |       |            |          |
|*  2 |   HASH JOIN SEMI    |        |     1 |    10 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TABLEA |     5 |    25 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TABLED |     1 |     5 |     3   (0)| 00:00:01 |
|   5 |   UNION-ALL         |        |       |       |            |          |
|*  6 |    TABLE ACCESS FULL| TABLEB |     1 |     5 |     3   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS FULL| TABLEC |     1 |     5 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( NOT EXISTS ( (SELECT /*+ */
              "TABLEB"."DOC_NUM","TABLEB"."DOC_TYPE" FROM "TABLEB" "TABLEB" WHERE
              LNNVL("TABLEB"."DOC_NUM"<>:B1) AND LNNVL("TABLEB"."DOC_TYPE"<>:B2))
              UNION ALL  (SELECT /*+ */ "TABLEC"."DOC_NUM","TABLEC"."DOC_TYPE" FROM
              "TABLEC" "TABLEC" WHERE LNNVL("TABLEC"."DOC_NUM"<>:B3) AND
              LNNVL("TABLEC"."DOC_TYPE"<>:B4))))
   2 - access("TABLEA"."DOC_NUM"="TABLED"."DOC_NUM" AND
              "TABLEA"."DOC_TYPE"="TABLED"."DOC_TYPE")
   6 - filter(LNNVL("TABLEB"."DOC_NUM"<>:B1) AND
              LNNVL("TABLEB"."DOC_TYPE"<>:B2))
   7 - filter(LNNVL("TABLEC"."DOC_NUM"<>:B1) AND
              LNNVL("TABLEC"."DOC_TYPE"<>:B2))


Statistics
__________________________________________________________
          8  recursive calls
          0  db block gets
         62  consistent gets
          0  physical reads
          0  redo size
        390  bytes sent via SQL*Net to client
        429  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

yogi@mydb.mu>