소속 회사는 그림 4-3과 같은 특정 기간 동안의 제출 보고서를 조회하는 화면이 다수 존재한다. 그리고 신청일자를 검색하는 SQL은 대부분 참고 4-14와 같이 구성되어 있으며 조회 시 문제없이 인덱스를 경유하여 관련 정보를 추출한다.

• [그림 4-3] 특정 기간 보고서 조회
• 

• [참고 4-14] 특정 기간 보고서 조회 쿼리 - 일반적인 경우

•  select *
   from 보고서제출이력
   where 신청일자 BETWEEN :검색시작일 AND :검색종료일 ;
   
  인덱스(IX_신청일자) : 신청일자
  

• 

그러나 일부 화면에서는 사용자가 신청일자를 제거하고 검색 버튼을 누르는 것을 대비하기 위해서 참고 4-15와 같이 SQL이 구성되어 있으며 티베로 옵티마이저는 바인드 변수에 NULL을 입력하는지 여부에 따라 대략 두 가지로 형태(4-15-1, 4-15-2)로 PLAN을 구성한다.

SQL 작성자는 바인드 변수를 입력하면 인덱스를 경유하고 입력하지 않는 경우(NULL)는 FULL SCAN을 타도록 옵티마이저가 알아서 분리 구성해주는 것을 기대하고 있으므로 4-15-2와 같이 항상 구성되어야 하는데, 바인드 변수를 입력하면 4-15-1과 같이 FULL SCAN을 타도록 PLAN이 구성되어 해당 SQL들의 성능저하가 심각하게 발생하였다.

더욱이 UNION ALL을 통해 상황에 따라 적합한 PLAN을 타기 위한 4-15-2의 분리 구성과 달리 두개 모두 동일한 FULL SCAN이 UNION ALL로 구성되어 있는 것으로 보아 옵티마이저를 매우 혼란스럽게 하는 SQL의 형태라고 생각이 된다(소속 회사가 사용 중이었던 외산 DBMS의 경우도 4-15-1과 같이 PLAN을 분리하지 못한 채 UNION ALL로 구성을 한 것으로 보아 제조사와 관계없이 혼란스러운 형태라고 판단된다. 다만 외산의 경우 바인드 변수와 관계없이 무조건 INDEX를 경유하도록 PLAN이 생성되어 티베로 보다 성능이 양호했다).

이 문제를 해결하기 위해 해당 화면의 어플리케이션 담당자와 논의결과 사용자가 신청일자를 제거하고 검색 버튼을 누르는 것을 대비할 필요가 없다라는 사실을 확인했다. 결국 요건에도 없는 사항을 개발자가 혼자 생각하여 조금 과한 친절을 베풀다가 발생한 문제로 판단되며 이와 같은 형태의 화면들은 모두 조건 없이 검색 버튼을 누를 경우 ‘신청일자를 반드시 입력하세요’라고 안내해주는 것을 화면에서 처리해 주기로 하였고 기간도 3개월 까지만 검색이 가능하도록 제한을 두었다. 그리고 해당 SQL은 NVL을 제거하는 방향으로 모두 수정해 주었다.

• [참고 4-15] 특정 기간 보고서 조회 쿼리 - BETWEEN 조건에 NVL을 함께 사용하는 경우

•  --과도한 조건을 수용하기 위한 비효율 SQL
   
  select *
  from 보고서제출이력
  where 신청일자 BETWEEN NVL(:검색시작일,'19000101') AND NVL(:검색종료일,'99991231)' ;
  
  인덱스(IX신청일자) : 신청일자
  

• 4-15-1) 바인드 변수 입력시 : 검색시작일 '20160101', 검색종료일 '20160131'

  4-15-2) 바인드 변수 미입력시 : 검색시작일 NULL , 검색종료일 NULL

만약 신청일자 조건이 NULL인 경우를 반드시 대비해야 한다면 다음과 같이 쿼리를 직접 분리해서 구성하는 것이 현명하다.

select /*+ index(a IX신청일자) */ *
from 보고서제출이력 a
where 신청일자 BETWEEN : 검색시작일 AND : 검색종료일
and : 검색시작일 is not null and : 검색종료일 is not null
UNION ALL
select /*+ full(보고서제출이력) */ *
from 보고서제출이력
where : 검색시작일 is null and : 검색종료일 is null

참고로 BETWEEN의 아닌 EQUAL 조건(=)에서는 UNION ALL을 통한 PLAN의 분리가 정확하게 이루어진다(참고 4-16)

• [참고 4-16] EQUAL 조건의 NVL 사용 시 PLAN의 정상 동작

• 
  1) EQUAL 조건 NVL 사용 - 바인드 변수 입력 시
  
  select *
  from 보고서제출이력
  where 신청일자 = NVL(:신청일, 신청일자);
  
  PLAN이 두개로 잘 분리되어 있고, 바인드 변수를 입력했으므로 인덱스를 경유한 플랜이 사용되었다.
  

• 

• 2) EQUAL 조건 NVL 사용 - 바인드 변수 미 입력 시(NULL)
  
  select *
  from 보고서제출이력
  where 신청일자 = NVL(:신청일, 신청일자)
  AND ROWNUM <=5;
  
  PLAN이 두개로 잘 분리되어 있고, 바인드 변수를 입력 하지 않았으므로 FULL SCAN 플랜이 사용되었다.
  

•