NOT IN, NOT EXISTS, MINUS의 효과적인 튜닝방법

BEST안은 적게 불러와서 적게 계산하는 것입니다. 
그 이외에도 여러가지 사항들이 많으나 몇가지만 적습니다.

- 검색조건과 무관한 인덱스 순서에 따른 문제 -> 검색 조건 우선순위에 따른 인덱스 컬럼 순서 재조정해서 재생성
- 데이터 제한 조건은 인덱스를 사용하도록
- 데이터 불러오는 양을 적게
- 사용자 ORACLE FUNCTION은 자제 (각 ROW단위로 연산이 일어나므로 매우 많은 CPU부하를 줌)
- 무조건 FULLTABLESCAN(이하 FTS)이 나쁜것은 아님
- NOT IN보다는 IN사용
- NOT EXISTS 보다는 EXISTS사용
- UNION보다는 UNION ALL사용

기본적으로는 CPU부담과 DISK I/O 를 적게 사용하는것이지만 서버의 CPU는 여유롭지만 DISK I/O가 많다면 DISK I/O를 줄여주는 방법이 좋겠고요 반대인 경우는 CPU사용을 줄여주는(연산작업) 방법이 좋은 튜닝 방법일것입니다.

위 세가지를 비교해 달라는 질문으로 알고 설명드립니다. 막상 질문사항만 놓고보자면 테이블크기, 데이터분포, 인덱스 컬럼 구성여부에 따라 상당히 다를 것입니다.

질문의 의도가 정확하게 나와 있지는 않으나 FTS를 한다는 것은 비교값이 인덱스컬럼에 적절히 설정되지 않았을 것 같은데요. 검색시 WHERE절에 들어가는 항목이 인덱스의 몇번째 컬럼인지 또한 그 분포도는 어떤지에 따라 FTS이 빠를 수도 있습니다.

우선은 해당 검색조건이 많은 데이터가 걸러지는 것인지 확인하여 인덱스를 추가/교체 구성할 필요가 있고 아니라면검색하고자 하는 조건이 인덱스 컬럼에 적정하게 설정되어 있다는 전제하에,

1. NOT IN은 각각의 데이터 자체를 비교하므로 CPU를 상대적으로 많이 사용하게 되며, 
2. NOT EXISTS는 KEY값(인덱스컬럼)만을 비교하므로 NOT IN에 비해여 자원을 덜 사용하게 될 것입니다.
    (하지만 때에 따라서는 NOT IN이 떠 빨리 결과를 내 줄수도 있습니다.)
3. 이와 달리 MINUS는 내부적으로 메모리내 SORT작업을 추가적으로 거치게 되고 일반적으로 서버자원을 좀 더 사용하므로 보통의 경우 많이 사용하지 않습니다.

결론적으로 말씀드리면 일반적인 SQL PLAN결과에 의한 튜닝기대효과는 NOT EXISTS > NOT IN > MINUS라고 볼수 있습니다. 하지만 테이블이 작은 경우라면 MINUS가 오히려 제일 빠른 결과를 내 줄 수도 있습니다.

부가적으로, FUNCTION BASED INDEX는 데이터 변경이 자주/많이 일어나는 경우,평상시 부하를 증가 시키므로 오히려 역효과가 날 수 있습니다. (해당 테이블을 사용하는 다른 SQL에 영향을 줄 수도 있고요...)
또한 정확한 조건MATCH가 일어나는 경우에만 인덱스를 사용하므로 해당 조건 추가시 효과에 대해 검토를 하여야 합니다.

※ 어느정도 개선되어 있는 상황이라면  tkprof를 사용하여 CPU자원을 많이 사용하는 것인지, DISK I/O를 많이 사용하는 것인지 판단하여 시간이 많이 걸리는/부하가 많이 걸리는 부분에 대한 개선을 하는것을 권장합니다.

질문의 의도를 정확히 판단하지 못하여 엉뚱한 답변이 되었을 수 있습니다. 양해바랍니다.



3. 실제로 NOT IN, NOT EXISTS, MINUS 부분은 인덱스를 타지 못하고 FTS(Full Table Scan)을 사용합니다.

오라클 교육을 받다보면, 아래와 같은 경우는 인덱스를 사용할수 없다고 나옵니다.

ⓛ NOT 연산자 사용
② IS NULL, IS NOT NULL 사용
③ 옵티마이저의 취사 선택
④ 외부적인 변형(External Suppressing)
⑤ 내부적인 변형(Internal Suppressing)

간단하게 정리를 해보았습니다.  참고하시길.
1. 연산자를 변경하여 처리하는 방법이 있습니다.
==> 경우에 따라서는 연산자 변경이 안될수도 있습니다. 특히 not 조건이라면 인덱스로 포함하려는 범위가 넓을것입니다. 

2. FTS와 인덱스 스캔 및 CBO vs RBO
 1) FTS로 수행한다고 무조건 느린것은 아닙니다.
    - 수행하실 테이블이 크지 않거나 칼럼 분포도가 좋지 않을경우는 오히려 FTS가 낫습니다. 
 2) Oracle 10g이상사용시 
   - 실제로 CBO를 사용하면, 비용개념으로 처리를 하기때문에 인덱스를 안타는 경우도 발생합니다. 
   - RBO를 사용하실 경우에는 실제로 계획된 대로 처리가 됩니다.
==> 10g 이상이라면,  Explain Plan을 해보시면 아실수 있을것입니다.
그리고,  테이블의 구조 및 상태에 따라 유동적이므로, 최적의 방법을 찾는것이 좋습니다. 

3. Not in 조건 사용시 
 1) 이 부분은 인덱스를 탈수 있는 연산자 변경을 하시면 가능합니다. 단 in 조건을 이용하셔서 그 값을 모두 뽑아도 된다면, 하나의 방법이 되겠지만, in에 조건값이 많아지면 그 성능은 그리 뛰어나다고 할수 없습니다.

종합적으로 보았을때, SQL 정보 및 시스템 상황을 고려해야할 경우라면 , 아래 두가지 정도입니다.
1) 성능상으로 크게 문제가 되지 않고, 해당 인덱스가 적용될 칼럼의 분포도 및 DML문(:insert ,update, delete)이 자주 발생하는 경우라면, CBO를 사용하여 비용단위로 추출된 쿼리로 FTS를 사용해도 괜찮을것입니다.(쿼리 실행 횟수도 체크가 필요할것입니다.)

2) 그러나, 해당 테이블이 대용량이고 select성으로 사용되는 테이블이고 ,해당 쿼리로 인하여 DB서버 및 AP에 부하가 우려되는 경우라면 말씀하신데로 연산자 변경이나 Function Based index를 사용하시는것이 좋을것입니다.