Applying two anti-join conditions separately speeds up a job

I was asked to create a temporary table on a 9.2.0.8 reporting database from the following sql:

select ot.TID
from sch_a.OT ot
where ot.TT_ID='C'
  and not exists (select 1 from sch_a.OT t
                  where ot.BID=t.BID and t.TT_ID='I')
   and not exists (select 1 from sch_b.BM bm
                  where ot.BID=bm.BID);

The execution plan looks like:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4640609 Card=360807 Bytes=5772912)
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'OT' (Cost=671732 Card=360807 Bytes=5772912)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'OT' (Cost=8 Card=1 Bytes=9)
   4    3       INDEX (RANGE SCAN) OF 'OT_IX3' (NON-UNIQUE) (Cost=4 Card=4)
   5    1     INDEX (UNIQUE SCAN) OF 'BM_PK' (UNIQUE) (Cost=3 Card=1 Bytes=7)

The first attempt to run this sql returned "snapshot too old" error after 21 hours.

Finally I took a two-step approach to accomplish the task. I am not sure it is the best way, however, task was accomplished in less than 5 hours.


1. create a temp table "tempa" by applying the first anti-join condition using outer-join





create table tempa nologging
as
  select ot.TID, ot.BID
    from sch_a.OT ot, sch_b.BM bm
   where ot.BID = bm.BID (+)
     and bm.rowid is null
     and ot.TT_ID = 'C'
   ;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1035475 Card=144322676 Bytes=4329680280)
   1    0   FILTER
   2    1     HASH JOIN (OUTER)
   3    2       TABLE ACCESS (FULL) OF 'OT' (Cost=671732 Card=144322676 Bytes=2309162816)
   4    2       INDEX (FAST FULL SCAN) OF 'BM_PK' (UNIQUE) (Cost=168790 Card=338111450 Bytes=4733560300)

2. Apply second anti-join condition using outer-join

select tmp.TID
 from  tempa tmp,
      ( select BID
        from  sch_a.OT t
       where t.TT_ID= 'I'
      ) a
 where tmp.BID = a.BID(+)
  and a.rowid is null;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=733333 Card=361891 Bytes=15199422)
   1    0   FILTER
   2    1     HASH JOIN (OUTER)
   3    2       TABLE ACCESS (FULL) OF 'TEMPA' (Cost=75 Card=226290 Bytes=5883540)
   4    2       TABLE ACCESS (FULL) OF 'OT' (Cost=671732 Card=144322676 Bytes=2309162816)

  ------ 

Look at the exection plans in the two-step approach, there are FTS and index fast full scan operations. One good thing about these operations is they appear in the v$session_longops view. The estimation of the exection time they need from this view is usually close to reality.