INDEX SCAN (MIN/MAX) 성능 개선 방안

이번에 소개할 내용은 오라클에서 제공하는 operation 중에 하나인 ‘INDEX FULL SCAN (MIN/MAX)’에 대해 살펴보고자 합니다. 

 [ 출처 – 오라클클럽 위키]

위의 그림은 인덱스 B-Tree를 그림으로 표현한 내용인데, INDEX FULL SCAN은 LEAF 블록을 처음부터 끝까지 모두 읽는 ‘수평적 탐색’을 의미합니다. 이 중 ’INDEX FULL SCAN (MIN/MAX)’  operation은 MIN() 또는 MAX() 함수로 데이터 추출 시 INDEX LEAF BLOCK의 양 끝(MIN/MAX)의 데이터를 읽어 빠르게 데이터를 추출하는 방법입니다. 이렇게 데이터를 추출할 경우 전체 데이터를 모두 읽지 않고 MIN/MAX 데이터를 빠르게 가져올 수 있어 성능을 극대화 시킬 수 있습니다.

‘INDEX FULL SCAN (MIN/MAX)’ operation이 어떻게 데이터를 액세스하는지 예제를 통해 살펴보도록 하겠습니다.

 

1. 테스트 데이터 생성

(MIN/MAX) operation을 살펴보기 위해 테스트 데이터를 만들도록 하겠습니다. 테스트 데이터는 ‘PARENT_T : CHILD_T = 1 : M’ 관계의 테이블을 만들고 CHILD_T 테이블에는 약 100만건의 데이터를 만들도록 하겠습니다.

테스트 환경은 오라클 11g이며, PARENT_T 테이블에는 7건의 코드성 데이터를, CHILD_T 테이블에는 숫자 자리수를 하나의 그룹으로 하고 여기에 날짜값이 순차적으로 증가하도록 CHILD_DATE 값을 넣었습니다. 또한, PARENT_T, CHILD_T 테이블에 각각 인덱스를 생성하였으며, CHILD_T의 경우 ‘CHILD_DATE’, ‘CNT + CHILD_DATE’ 2개 인덱스를 생성하였습니다. 아래는 각각의 테이블 데이터 출력 결과입니다.

 

2. (MIN/MAX) operation

위에서 만든 샘플 데이터를 통해 (MIN/MAX) operation을 살펴보도록 하겠습니다. 우선 일반 그룹함수인 SUM() 함수를 이용하여 MIN/MAX() 함수와 어떤 차이가 있는지 보겠습니다.

보시는 것처럼 100만건이 존재하는 CHILD_T 테이블에 SUM() 함수를 사용할 경우 당연히 100만건을 모두 액세스하고 SUM을 구해야 합니다. 하지만 MAX() 함수의 경우 인덱스에서 딱 1건만 액세스하고 바로 데이터를 가져오고 있으며, 수행시간 및 I/O도 약 1,000배 감소된 걸 볼 수 있습니다.

하지만 (MIN/MAX) operation은 MIN, MAX 값을 동시에 가져올 수는 없습니다. 만약 MIN, MAX 함수를 동시에 사용할 경우 어떻게 되는지 살펴보겠습니다.

위의 결과처럼 MIN, MAX 함수를 동시에 사용할 경우  (MIN/MAX) operation은 실행계획에 나타나지 않으며, TABLE FULL SCAN으로 전체 데이터를 액세스하여 가져오는 것을 볼 수 있습니다. 그럼 MIN, MAX 값을 동시에 가져오면서 (MIN/MAX) operation을 사용하려면 어떻게 해야 할까요?

방법은 바로 MIN과 MAX를 가져오는 SQL을 각각 구현한 후 이를 UNION ALL로 묶어 데이터를 추출할 수 있습니다.  즉, MIN/MAX는 동시에 추출할 수 없다는 것을 이 테스트를 통해 확인할 수 있습니다.

(MIN/MAX) operation의 장점은 테이블에 조건이 없어도 INDEX FULL SCAN을 통해 데이터를 빠르게 가져온다는 장점이 있는데, 만약 조건이 들어갈 경우 operation에 어떤 변화가 있는지 살펴보겠습니다. 아래 예제는 CHILD_T 테이블에 CNT 조건을 추가하여 MAX(CHILD_DATE) 값을 가져오는 SQL입니다.

CHILD_T 테이블에 CNT=5 조건을 추가하여 MAX(CHILD_DATE) 값을 구할 경우에도 마찬가지로 빠르게 한건을 가져오고 있는데, 조건이 안 들어간 경우와 비교하여 실행계획에 변화가 생겼습니다. 변화가 생긴 operation은 바로 ‘INDEX FULL SCAN (MIN/MAX)’에서 ‘INDEX RANGE SCAN (MIN/MAX)’으로 바뀐 점입니다. 즉, MIN/MAX operation의 경우 조건이 없을 경우 INDEX FULL SCAN을 통해 양 끝의 데이터를 가져올 수 있으며, 조건이 있을 경우 해당 조건을 INDEX RANGE SCAN으로 액세스 한 뒤 해당 인덱스의 양 끝 데이터를 또한 MIN/MAX로 가져올 수 있다는 것을 의미합니다.

 

3. (MIN/MAX) operation 튜닝

그럼 CNT 값을 조건으로 넣기 않고 CNT 값에 따른 MAX_CHILD_DATE 값을 가져오려고 하면 어떻게 될까요? 아래의 예제에서 살펴보겠습니다.

보시는 것처럼, TABLE FULL SCAN을 통해 100만건을 모두 액세스한 후 데이터를 추출하는 것을 볼 수 있습니다. 즉 CHILD_T_N02(CNT, CHILD_DATE) 인덱스를 활용하여 MIN/MAX operation을 사용하지 못하는 것을 확인할 수 있습니다.

그럼 CNT 값에 값을 직접 상수로 입력할 경우 MIN/MAX operation을 사용할 수 있는지 살펴보겠습니다.

보시는 것처럼 CNT 값을 상수로 넣을 경우 TABLE FULL SCAN이 아닌 INDEX RANGE SCAN으로 operation이 변경되었지만 여전히 MIN/MAX operation으로 데이터를 가져오지 않기 때문에 100만건을 액세스하는 것은 변함이 없습니다. 여기서 두 번째 특징을 찾을 수 있는데 MIN/MAX operation은 반드시 특정값에 속한 경우에만 MIN/MAX 값을 가져올 수 있다는 것입니다. CHILD_T 테이블에서 조건없이MAX(CHILD_DATE) 값을 추출할 경우 ‘CHILD_T_N01(CHILD_DATE)’ 인덱스를 통해 ‘INDEX FULL SCAN (MIN/MAX)’  operation을 사용할 수 있으며, CNT 값이 들어갈 경우 ‘CHILD_T_N02(CNT, CHILD_DATE)’ 인덱스를 통해 특정 선두값(CNT)에 대해서만 ‘INDEX RANGE SCAN (MIN/MAX)’을 사용할 수 있습니다. 하지만 선두값이 여러개일 경우 MIN/MAX operation을 사용할 수 없으며 전체 데이터를 모두 읽어야 하는 한계가 있습니다.

이와 같이 선두 컬럼을 여러개 추출해야 할 경우 MIN/MAX operation을 이용하여 빠르게 데이터를 추출할 수 있는 방법은 없는것일까요? 방법이 있습니다. 그것은 바로 위에서 나열한 특정을 활용하는 것인데, ‘INDEX RANGE SCAN (MIN/MAX)’을 사용하려면 특정 선두값을 반드시 한건만 입력해야 한다는 것을 알았으므로 이 특징을 활용하도록 SQL을 변경해주면 됩니다. 아래의 예제에서 살펴보겠습니다.

CHILD_T 테이블에 선두컬럼을 넣으려면, ’PARENT_T : CHILD_T = 1 : M’ 관계를 활용하면 되며 이를 1:1 관계로 만들 경우, 즉 CHILD_T 테이블을 스칼라 서브쿼리로 변경할 경우 성능 선두컬럼(CNT)을 항상 받을 수 있기 때문에 MIN/MAX operation을 이용할 수 있으며, 이와 같이 극대화된 성능 개선이 가능합니다.

 

4. MIN/MAX operation 정리

이처럼 MIN/MAX operation 특징을 정확히 이해하고 활용할 경우 성능개선이 가능하며 이 기능을 정리한 내용은 아래와 같습니다.

 

지금까지 MIN/MAX operation에 대해 살펴보았습니다

내용을 보시고 궁금하신 점이 있으시면 리플 남겨주시기 바랍니다.

감사합니다.