우리가 SQL을 작성하다 보면 많은 업무에서 최대값과 최소값을 추출해야 하는 경우가 생겨난다. 최대값과 최소값은 조건을 만족하는 데이터를 하나만 엑세스해서 값을 추출할 수 없는 개념이기 때문에 조건을 만족하는 모든 데이터를 엑세스해야 한다.

따라서 잘못하면 성능 저하를 발생시키는 주범이 될 수 있다. 이와 같은 최대값과 최소값을 추출하는 SQL을 최적화하기 위해서는 인덱스관련 실행 계획을 잘 이용해야 할 것이다.

SQL의 최적화를 수행하기 위해서는 SQL의 실행 계획이 가장중요하다. 최대값과 최소값을 추출하는 SQL 또한 이와 같은 실행 계획을 통해 최적화할 수 있다.

그러므로 최대값과 최소값을 추출하는 SQL은 인덱스 관련 실행 계획과 매우 밀접한 관계를 가지게 된다.

인덱스를 어떻게 이용하는가에 따라 우리는 전혀부하를 발생시키지 않고 최대값과 최소값을 추출할 수 있게 된다. 이번 강의에서는 인덱스 실행 계획을 이용해 최대값과 최소값 을 효율적으로 추출하는 방법을 함께 확인해 보자.

기존의 최대값과 최소값을 추출하는 방법을 이해하자

보통 많이 사용하고 자주 사용하는 방법을 통해 문제점을 파악하는 것이야말로 최적의 SQL을 작성하는 지름길 일 것이다. 기존의 SQL이 어떤 문제점을 가지고 있는지 이를 이해하는 것이야 말로 SQL 최적화의 기본이다.

SELECT 사원이름, MAX(급여) 최대급여
  FROM 사원;

사원 테이블에는 100,000건의 데이터가 존재한다고 가정하자. 이와 같다면 해당 SQL은 어떻게 수행되겠는가?

사원 테이블의 100,000건의 데이터를 모두 엑세스해서 급여 컬럼의 값을 모두 비교하여 최대값인 하나의 값만을 추출하게 될 것이다.

그렇다면 당연히 100,000건의 데이터를 모두 엑세스할 것이다. 100,000건의 데이터를 엑세스하는데 과연 1초만에 결과가 추출될 수 있을 것인가? 이는 현존하는 어떤 기술을 사용해도 불가능한 일이다.

해당 SQL은 수십 초 동안 수행될 수도 있다. 그렇다면 해당 SQL은 어떻게 최적화해야 하는가? 하나의 예제를 추가로 확인해 보자

SELECT MAX(번호)+1
  FROM 사원
 WHERE 부서번호 = '10';

위와 같은 SQL은 어디선가 많이 보았을 만한 SQL이다. 해당 SQL은 부서번호별로 최대값 + 1의 값을 할당해 번호를 할당하는 SQL에 해당한다.

해당 SQL에서 부서번호 컬럼의 값이 '10'을 만족하는 데이터가 1,000건 존재한다면 한 건만 엑세스하고 10번 부서번호의 최대 번호 컬럼의 값을 추출할 수 있겠는가? 기존 SQL에서는 쉽지 않은 일이다.

위에서 확인한 SQL은 추출되는 데이터에 비해 많은 데이터를 엑세스해야 하기 때문에 자칫 잘못하면 성능 저하를 발생시키는 주범이 될 수 있다.

이와 같은 SQL은 어떻게 최적화해야 하는지 이제부터 그 해답을 찾아보자.

최적의 최대값과 최소값을 추출하는 방법을 이해하자

앞서 소개한 최대값을 추출하는 SQL에 대해 최적화하는 방법을 확인해 보자.

SELECT 사원이름, MAX(급여) 최대급여
  FROM 사원;

위의 SQL에서 전체 데이터를 엑세스하지 않고 최대값을 추출하기 위해서는 어떻게 해야 하는가?

데이터가 급여 컬럼의 값으로 정렬되어 있어서 그 중에 맨 아래 있는 값을 하나 추출한다면 해당 값은 사원 테이블에서 급여 컬럼의 최대값이 될 것이다. 그렇다면 어디에 급여 컬럼의 값으로 정렬된 데이터가 존재하는가?

어차피 테이블은 저장되는 데이터의 순으로 저장되므로 급여 컬럼의 값으로 정렬되어 있다는 보장이 없다. 급여 컬럼의 값으로 정렬되어 있는 요소는 바로 인덱스이다.

급여 컬럼으로 인덱스를 생성한다면 급여 컬럼의 값으로 자동 정렬되어 있게 된다. 이를 이용한다면 우리는 최소의 데이터 엑세스로 최적의 성능을 보장하며 최대값을 추출할 수 있을 것이다.

급여 컬럼으로 생성된 인덱스는 어떠한가?

급여 컬럼의 값으로 정렬되어 있으므로 인덱스의 마지막 값이 최대값이 될 것이다. 따라서 아래와 같이 SQL을 수행한다면 한 건의 데이터만을 엑세스하고 최대값을 추출하게 된다.

SELECT /*+ INDEX_DESC(사원 IDX1) */ 사원이름, 급여 최대급여
  FROM 사원
 WHERE ROWUM = 1;

위의 SQL은 어떻게 수행되는 것인가?

힌트에 사용된 IDX1 인덱스는 급여 컬럼으로 생성된 인덱스라고 가정하자. 그렇다면 힌트에 의해 사원 테이블을 급여 컬럼으로 생성된 인덱스를 이용해 결과를 추출하게 될 것이다.

힌트에 INDEX_DESC로 되어있으므로 인덱스의 밑에서부터 엑세스를 시작하게 된다. 인덱스의 밑에서부터 데이터를 엑세스하므로 급여 컬럼의 값이 가장 큰 데이터부터 차례대로 추출된다.

WHERE 조건의 ROWNUM =1에 의해 결국 처음 추출되는 급여 컬럼의 최대값 하나만이 결과로 추출될 것이다.

이와 같이 수행한다면 해당 테이블의 100,000건의 데이터를 엑세스하는 것이 아니라 단지 한 건의 데이터만을 엑세스하게 되므로 최적의 성능을 보장받을 수 있게 될 것이다.

두 번째 SQL은 아래와 같이 최적화를 수행할 수 있다

SELECT /*+ INDEX_DESC(사원 IDX1) */ MAX(번호)+1
  FROM 사원
 WHERE 부서번호 = '10'
   AND ROWNUM = 1;

IDX1 인덱스는 부서번호+번호 인덱스라고 가정하자. 그렇다면 부서번호 조건에 의해 해당 인덱스에서 10번 부서번호의 값만을 엑세스하게 된다.

인덱스가 두 개의 컬럼으로 구성된다면 첫 번째 컬럼으로 정렬되며 첫 번째 컬럼의 값이 동일하다면 두 번째 컬럼에 의해 정렬이 발생하게 된다. 따라서 동일한 부서번호 값을 가지는 데이터는 번호 컬럼으로 정렬되며 해당 SQL은 힌트에 의해 10번 부서번호 데이터에 대해 밑에서부터 데이터를 엑세스하게 된다.

따라서 10번 부서번호에 대해 최대 번호 값이 추출되며 ROWNUM = 1에 의해 추출되는 데이터 중 가장 먼저 엑세스되는 한 건의 데이터만이 결과로 추출된다. 10번 부서 번호에 대해 최대 번호 값부터 추출되며 한 건만 추출되고 종료되므로 최대 번호 값이 된다.

이와 같이 SQL을 수행한다면 10번 부서번호 값을 만족하는 1,000건의 데이터를 모두 엑세스하는 것이 아니며 단지 한 건의 데이터만을 엑세스하게 되므로 성능을 보장할 수 있게 된다.

이와 같은 방법을 통해 우리는 최대값과 최소값을 추출하는 SQL을 최적화해왔다. 하지만 데이터베이스가 진화하면서 이렇게 수행하지 않고도 이처럼 수행될 수 있는 인덱스가 존재한다면 데이터베이스의 옵티마이저가 이를 대신하게 되었다.

그것이 인덱스 관련 실행 계획 중 MIN/MAX 실행 계획이다.

우리가 기존 방식대로 SQL을 작성하고 실행 계획을 확인하는 경우에 종종 MIN/MAX 실행 계획을 볼 수 있을 것이다.

이는 기존 방식의 SQL을 옵티마이저가 최적의 최대값 또는 최소값을 추출하는 SQL로 변형해 수행하게 되는 것이다.

따라서 이제 우리가 해줘야 할 일은 SQL의 변경이 아니라 변경되었을 경우 반드시 필요한 인덱스만 만들어주면 실행은 옵티마이저가 판단해 최적의 실행을 수행할 수 있게 된 것이다.

이 얼마나 옵티마이저가 진보한것인가?

옵티마이저의 진보한 실행 계획을 최적으로 이용하기 위해 우리가 해줘야 할 것은 최적의 인덱스를 준비해줘야 한다는 것이며 이를 잊어서는 안 된다.