꿈꾸는 개발자, DBA 커뮤니티 구루비

프로젝트에서 개발을 수행하다 보면 애플리케이션의 적지 않은 부분에서 'IS NULL' 구문을 사용하게 된다. 대부분의 개발자들은 이 구문이 적절한 인덱스를 액세스한다면 문제가 되지 않는다고 생각하지만, 이는 잘못된 생각이다.

기본적으로 인덱스의 각 노드는 NULL 값을 포함하지 않는다. 때문에 조건이 NULL 값인 행을 인출하기 위해 NULL 값이 포함되지 않는 인덱스를 액세스할 수 없다.

결과적으로 'WHERE column_name IS NULL'에 대한 조건은 FULL TABLE SCAN에 의해 불필요한 데이터 블록을 과다하게 액세스하는 성능 이슈를 발생시키게 된다.

다음의 예제를 살펴보자

SELECT 전화번호, 메시지, 전송일자, 요청일자
  FROM SMS_MESSAGE
 WHERE 전송일자 IS NULL

위 SQL은 전송되지 않은 SMS 단문 메시지 행을 검색해 전송 요청을 수행하는 구문이다.

'전송일자'칼럼에는'NULL'과 'DATE'두 종류의 데이터 유형이 존재하며, 전송 전에는 NULL 값이 전송 후에는 전송이 완료된 날짜 데이터가 갱신되게 된다.

또한 적재된 데이터의 대부분은 전송이 완료된 날짜 값이 존재하고 있다. 이때 전송되지 않은 소량의 데이터를 추출하기 위해 전송일자에 인덱스를 생성하고 위와 같은 SQL을 수행한다면, 해당 인덱스는 무용지물이 된다.

이유는 간단하다. 위에서 언급했듯이 인덱스는 NULL을 포함하지 않기 때문에 조건에 대한 인덱스를 액세스하지 못하고, FULL TABLE SCAN에 의한 테이블 전체 액세스를 시도하게 된다.

문제가 되는 위 SQL에 대한 실행 계획은 다음과 같다.

Execution Plan
------------------------------------------------------------------------------
Plan hash value: 1445457117
------------------------------------------------------------------------------
| Id | Operation        | Name        | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
| 0  | SELECT STATEMENT |             | 1    | 68    | 3 (0)      | 00:00:01 |
|* 1 | TABLE ACCESS FULL| SMS_MESSAGE | 1    | 68    | 3 (0)      | 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------------------------------------------
1 - filter("전송일자" IS NULL)

그렇다면 이와 같은 조건에 대한 해결책은 없는가? 인덱스의 NULL 값을 포함할 수 없는 특성을 이미 파악했다면, NULL을 제거하는 것으로 FULL TABLE SCAN을 회피할 수 있다.

이렇게 원인은 파악되었고, 그렇다면 이제부터 NULL을 제거하는 방법에 대해 알아보자.

첫 번째로 전송 전 데이터 행의'전송일자'칼럼에 NULL이 아닌 값을 갱신하면 된다. 하지만 데이터 유형이 시간을 표현하는 DATE 타입이기 때문에 시간이 아닌 의미 없는 데이터 값을 갱신하기가 쉽지는 않다.

두 번째로 데이터 유형을'DATE'타입이 아닌'VARCHAR2'와 같은 캐릭터 유형으로 변경하는 것이다.

하지만 이미 운영 중인 시스템의 데이터 유형을 변경시키는 것도 쉽지 않을 뿐더러, 시간 데이터가 적재되는 칼럼을 캐릭터 타입으로 변경했을 시 발생하는 문제점, 가령 오라클 옵티마이저가 잘못된 실행계획을 세우거나 날짜 연산의 오류 등을 감안한다면 좋은 해결책이 될 수 없다.

세 번째로 함수기반 인덱스(Function Based Index)를 활용하는 것이다.

함수기반 인덱스는 칼럼의 데이터 값에 함수를 적용한 반환 값을 기반으로 인덱스를 생성하기 때문에 해당 칼럼의 데이터 유형에 관여치 않을 뿐 아니라, 기존에 적재된 데이터도 변경시키지 않는다.

또한 동일'전송일자'칼럼에 대해 함수기반 인덱스가 아닌 일반적인 인덱스를 동일 칼럼에 중복으로 생성할 수 있으므로 함수로 변형되지 않은 원래의 데이터를 인덱스로 액세스하는 SQL 구문도 문제없이 수행할 수 있다.

다음의 예를 살펴보자.

CREATE INDEX FBI_SMS_전송일자
    ON SMS_MESSAGE(NVL2('전송일자','전송', '미전송'));

위 SQL은'전송일자'칼럼의 값 유형이'NOT NULL'일 경우'전송'값으로, 'NULL'일 경우'미전송'값으로 반환하는 NVL2 함수를 적용해 그 값을 기반으로 인덱스를 생성하는 예제다.

물론 반환 값은 날짜 유형이 아닌 캐릭터 유형이고, 생성된 인덱스도 캐릭터 값에 기반한 인덱스다. 또한 '전송일자' 칼럼에 존재하는 데이터와 날짜 데이터 유형도 변경되지 않았다.

그렇다면 생성된 함수기반 인덱스가 원하는 실행계획으로 수행되는지 알아보자.

SELECT 전화번호, 메시지, 전송일자, 요청일자
  FROM SMS_MESSAGE
 WHERE NVL2('전송일자', '전송', '미전송') = '미전송'

해당 SQL은 미 전송된 메시지 데이터 행을 추출하기 위해 '전송일자'칼럼에 함수기반 인덱스에 적용한 것과 같은 NVL2 함수를 적용해 조회작업을 수행한다.

전송되지 않는 SMS 메시지 행을 얻기 위해 조회 조건의 SQL 구문은 변경되었지만, 추출되는 데이터 행의 결과값은 동일하다.

다음은 함수기반 인덱스를 액세스하는 SQL 구문의 내부 실행계획이다.

Execution Plan
-----------------------------------------------------------------------------------
Plan hash value: 1467806439
-----------------------------------------------------------------------------------
| Id | Operation                 | Name           |Rows|Bytes|Cost (%CPU)| Time   |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT           |                |1   | 27  | 2 (0)     |00:00:01|
| 1 | TABLE ACCESS BY INDEX ROWID|SMS_MESSAGE     |1   | 27  | 2 (0)     |00:00:01|
|* 2| INDEX RANGE SCAN           |FBI_SMS_전송일자|1   |     | 1 (0)     |00:00:01|
-----------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------------------------------------
2 - access(NVL2("전송일자",'전송','미전송')='미전송')

위 실행계획에서 보이는 결과대로 해당 SQL이 인덱스를 액세 스함을 확인할 수 있다. 물론 조회 성능이 획기적으로 향상된 것 도 확인할 수 있다.

이예제를보면서'전송',' 미전송'의 두가지 데이터 유형, 즉 데이터 분포도가 낮은 특성의 칼럼에 인덱스를 사용하는 것에 대한 의문을 제기할 수 있다.

일반적으로'성별'같은 데이터 분포도가 낮은 칼럼에 대해서는 인덱스를 생성하지 않는다는 선입견이 있는데, 이것이 절대적인 것은 아니다.

인덱스는 테이블의 칼럼에 생성하지만, 인덱스의 생성 이유와 생성 형태는 SQL의 조건이어야 한다.

예를 들어 큰 사이즈 테이블의 '성별' 칼럼에 대부분의 데이터가 '남자'이고 100건 미만의 소량 데이터만 '여자'라고 가정한다면 '여자'데이터 행의 빠른 액세스를 위해 이 칼럼에는 반드시 인덱스가 필요하다.

이와 마찬가지로 전송되지 않은 SMS 메시지 데이터 행의 빠른 액세스를 위해 함수기반 인덱스를 적절히 사용할 수 있는 예를 보여준 것이다.

많은 프로젝트에서 데이터베이스의 NULL 값은 의도하지 않게 많은 사건과 사고를 만들어낸다. 또한 NULL 값은 SQL 성능뿐만 아니라 의도하지 않은 잘못된 조회 결과 값을 만들어내기도 한다.

이는 데이터 아키텍트 수립 과정에서부터 잘못된 것일 수 있으며, 그 이외의 다른 부분에서도 원인을 찾을 수 있다.

프로젝트 개발자들은 항상 NULL 값에 대한 두려움을 인지해야 하며, NULL 값으로 인해 발생하는 문제에 대해 효과적으로 대응할 기술을 쌓아야 할 것이다.