2009. 8. 20. 11:22

[Oracle] 부정형(NOT IN, <>, NOT EXISTS ...)의 비교


PURPOSE
 
부정형의 비교를 긍정형 비교로 바꾸어서 인덱스 사용을 유도하는 방법에 대해서 알아본다.


KEY IDEA
 
부정형의 비교에는 논리적으로 인덱스를 사용할 수 없다. 하지만 약간의 IDEA를 첨부한다면
부정형의 비교를 긍정형의 비교로 바꾸어서 인덱스의 사용을 유도할 수 있다.
(KEY WORD : INDEX 활용, 인덱스, 부정형 비교, NOT IN, NOT EXISTS, <> )


DESCRIPTION
  
다음의 SQL을 보자.
     SELECT ‘Not found’  FROM EMP WHERE EMPNO <> ‘1234’

  • 대개의 Application에서는 사용자가 처리한 데이터의 타당성을 검증하기 위해 이 값의
    존재 유무를 확인하는 경우가 빈번하게 발생한다.  
    이럴 경우 위의 예처럼 부정형의 문장을 사용하는 경우가 자주 있다.

  • 하지만 아래와 같이 ‘NOT EXISTS’를 이용해서 서브쿼리(SUB-QUERY)내의 SQL을 긍정형으로 바꾸면 인덱스를 사용할 수 있다.

     SELECT ‘NOT FOUND’ FROM DUAL
        WHERE NOT EXISTS ( SELECT ‘X’ FROM EMP WHERE EMPNO = ‘1234’ )

  • 그러나 ‘EXISTS’를 사용하는 것이 항상 유리한 것은 아니다. 다음의 3개의 SQL을 보자.

    [SQL1]
      SELECT * FROM TAB1
         WHERE YYYYMM = ‘199910’
             AND NOT EXISTS ( SELECT * FROM TAB2
                                       WHERE COL2 = COL1
                                           AND YYYYMM = ‘199910’ )
    [SQL2]
      SELECT * FROM TAB1
          WHERE YYYYMM =’199910’
              AND COL1 NOT IN (SELECT COL2 FROM TAB2
                                           WHERE YYYYMM = ‘199910’ )
    [SQL3]
       SELECT * FROM TAB1
           WHERE (YYYYMM, COL1) IN ( SELECT ‘199910’, COL1 FROM TAB1
                                                       WHERE YYYYMM = ‘199910’
                                                   MINUS
                                                   SELECT ‘199910’, COL2 FROM TAB2
                                                        WHERE YYYYMM = ‘199910’ )

  • TAB1 테이블의 ‘YYYYMM’, ‘COL1’이 각각 인덱스로 생성되어 있고
    TAB2의 ‘YYYYMM’, ‘COL2’가 각각 인덱스로 생성되어 있다.
     
  • [SQL1] 은 ‘TAB1’의 ‘YYYYMM’ 인덱스만을 사용하여 테이블의 로우를 엑세스하고
    각 로우마다 TAB2 테이블을 엑세스하는 서브쿼리가 수행되어 TAB2 에 존재하지 않는
    로우만 추출하게 된다. 이 SQL은 ‘199910’조건에 해당하는 모든 로우에 대해 서브
    쿼리가 랜덤엑세스를 수행한다. 왜냐하면 서브쿼리내에 메인쿼리의 컬럼인 'COL1'이
    존재하기 때문
    이다.
     
  • [SQL2] 는 서브쿼리 내에 메인쿼리 컬럼을 없애기 위해 작성하였지만 동일한 결과를
    초래한다. 그 이유는 'NOT IN'을 사용한 서브쿼리는 항상 나중에 수행되거나
    필터링(Filtering) 조인방식으로 수행되기 때문
    이다.
     
  • [SQL3]은 각 테이블에 ‘YYYYMM + COL1’, ‘YYYYMM + COL2’의 결합인덱스가 존재한
    다면 먼저 서브쿼리에서 두 개의 테이블을 ‘MINUS’하여 결과를 추출하고 그 결과를
    이용해 메인쿼리를 엑세스하게 할 수 있다. 이 경우에는 서브쿼리가 먼저 수행된다.
    인덱스만으로도 처리가 가능하기 때문에 테이블을 엑세스하지 않고 양쪽 테이블의
    인덱스들만 범위스캔(Range Scan)하여 ‘SORT-MERGE’방식으로 서브쿼리가 처리 된다.
     
  • 위의 경우에서는 결과적으로 [SQL3]가 가장 유리한 처리방법이라 하겠다.

참고 : http://kdonghwa.tistory.com/58?srchid=BR1http%3A%2F%2Fkdonghwa.tistory.com%2F58