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

[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://www.en-core.com]