[Oracle] INTERSECT, MINUS -> JOIN, NOT EXISTS

1. before SQL & plan

DELETE      TIMAGECOMPLETE
      WHERE (OWNERID, ORDERID) IN (
               (SELECT OWNERID, ORDERID
                  FROM TIMAGECOMPLETE
                INTERSECT
                SELECT OWNERID, ORDERID
                  FROM TTASKTRANSHI)
               MINUS
               SELECT OWNERID, ORDERID
                 FROM TTASKTRANS)
Execution Plan
--------------------------------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=260K Card=3K Bytes=108K)
   1    0   DELETE OF 'TIMAGECOMPLETE'
   2    1     NESTED LOOPS (Cost=260K Card=3K Bytes=108K)
   3    2       VIEW OF 'SYS.VW_NSO_1' (Cost=260K Card=3K Bytes=62K)
   4    3         MINUS
   5    4           INTERSECTION
   6    5             SORT (UNIQUE) (Card=3K Bytes=46K)
   7    6               INDEX (FAST FULL SCAN) OF 'TIMAGECOMPLETE_PK' (UNIQUE) (Cost=3 Card=3K Bytes=46K)
   8    5             SORT (UNIQUE) (Card=32M Bytes=549M)
   9    8               TABLE ACCESS (FULL) OF 'TTASKTRANSHI' (Cost=133K Card=32M Bytes=549M)
  10    4           SORT (UNIQUE) (Card=378K Bytes=6M)
  11   10             TABLE ACCESS (FULL) OF 'TTASKTRANS' (Cost=2K Card=378K Bytes=6M)
  12    2       INDEX (UNIQUE SCAN) OF 'TIMAGECOMPLETE_PK' (UNIQUE) (Card=1 Bytes=18)
 

1.1 문제점 및 해결책

  - INTERSECT 와 MINUS 인해 TTASKTRANSHI, TTASKTRANS 테이블에 대해  full table scan이 발생하여 성능저하 현상이 발생과 동시에 Lock holding이 발생함.
  - INTERSECT와 MINUS를 join 및 not exists로 변경하여 성능개선 가능.
  - 업무담당자와 협의 및 데이터 검증 필요.
  
 
2. After SQL & plan

DELETE      TIMAGECOMPLETE
      WHERE (OWNERID, ORDERID) IN (
               SELECT A.OWNERID, A.ORDERID
                 FROM TIMAGECOMPLETE A, TTASKTRANSHI B
                WHERE 1 = 1
                  AND A.OWNERID = B.OWNERID
                  AND A.ORDERID = B.ORDERID
                  AND NOT EXISTS (
                         SELECT 1
                           FROM TTASKTRANS C
                          WHERE 1 = 1
                            AND A.OWNERID = C.OWNERID
                            AND A.ORDERID = C.ORDERID))
                       
Execution Plan
--------------------------------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=8K Card=1 Bytes=42)
   1    0   DELETE OF 'TIMAGECOMPLETE'
   2    1     MERGE JOIN (SEMI) (Cost=8K Card=1 Bytes=42)
   3    2       INDEX (FULL SCAN) OF 'TIMAGECOMPLETE_PK' (UNIQUE) (Cost=12 Card=3K Bytes=46K)
   4    2       SORT (UNIQUE) (Cost=8K Card=2M Bytes=37M)
   5    4         VIEW OF 'SYS.VW_NSO_1' (Cost=5M Card=2M Bytes=37M)
   6    5           FILTER
   7    6             NESTED LOOPS (Cost=136 Card=2M Bytes=55M)
   8    7               INDEX (FAST FULL SCAN) OF 'TIMAGECOMPLETE_PK' (UNIQUE) (Cost=3 Card=132 Bytes=2K)
   9    7               INDEX (RANGE SCAN) OF 'TTASKTRANSHI_IDX02' (NON-UNIQUE) (Cost=3 Card=12K Bytes=214K)
  10    6             INDEX (RANGE SCAN) OF 'TTASKTRANS_IDX02' (NON-UNIQUE) (Cost=3 Card=1 Bytes=18)