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

목록 쿼리는 많이 사용할 수밖에 없는 것이 현실이다. 많은 웹 화면에서는 여러 가지 형태의 목록 쿼리가 존재한다. 이러한 목록 쿼리는 쉬운 듯 하면서도 매우 어려운 것이 현실이다. 이와 같은 목록 쿼리의 최적화야말로 전체 시스템의 성능 향상을 좌우할 수 있는 기준이 될 것이다.

지난 강의에는 목록 쿼리에 대해 전체 데이터 처리 방식을 확인해 봤다. 이번 강의에서는 전체 데이터 처리 방식의 SQL을 n-Row 처리 방식으로 변경하는 방법을 확인해 보자. 목록 쿼리를 n-Row 처리 방식으로 변경하는 것은 해당 목록쿼리를 최적화할 수 있는 방식이다. 이와 같은 방식에 대한 이해는 SQL을 최적화하기 위해 반드시 필요할 것이다.

전체 데이터 처리 방식의 목록 쿼리를 n-Row 처리 방식으로 변경하자

전체 데이터 처리의 목록 쿼리를 n-Row 처리 방식으로 변경하는 방법을 확인해 보자. 전체 데이터 처리의 목록 쿼리를 n-Row 처리의 목록 쿼리로 변경하기 위해서는 앞에서도 언급했듯이 ORDER BY 절을 제거해야 한다

SELECT 종류, 제목, 도착시간, 크기
  FROM 
     ( SELECT ROWNUM 순번, 종류, 제목, 도착시간, 크기
         FROM 
            ( SELECT 번호, 종류, 제목, 도착시간, 크기
                FROM 메일_내역
               WHERE 메일_ID= 'ABC'
               ORDER BY 도착시간 DESC
            )
     )
 WHERE 순번 BETWEEN 1 AND 10
   AND ROWNUM <= 10;

그렇다면 위의 쿼리에서 어떻게 ORDER BY 절을 제거할 수 있을까? 전체 데이터 처리의 목록 쿼리를 n-Row 처리의 목록 쿼리로 변경하는 첫 걸음은 ORDER BY 절을 제거하는 것부터 시작하게 된다.

SELECT 종류, 제목, 도착시간, 크기
  FROM 
     ( SELECT ROWNUM 순번, 종류, 제목, 도착시간, 크기
         FROM 
            ( SELECT 번호, 종류, 제목, 도착시간, 크기
                FROM 메일_내역
               WHERE 메일_ID= 'ABC'
            )
     )
 WHERE 순번 BETWEEN 1 AND 10;

위와 같이 ORDER BY 절만을 생략한다면 정렬된 데이터가 추출되지 않게 된다.

순번을 1부터 10,000번까지 할당한다면 ORDER BY 절을 사용한 경우에는 가장 최근 데이터가 1번의 값을 할당 받고 가장과거 데이터가 10,000번의 값을 할당 받게 된다.

하지만, 위의 경우는 메일_내역 테이블에 저장된 KKK 메일_아이디의 데이터를도착시간 순으로 순번을 할당하지 않게 된다. 그렇기 때문에 우리가 원하는 도착시간 순서로 데이터가 추출되지 않게 된다.

정렬된 데이터가 추출되지 않는다면 위의 쿼리로 목록 쿼리를 대신할 수는 없을 것이다. 그렇다면 어떻게 ORDER BY 절을 사용하지 않고 정렬된 데이터를 추출하는 목록 쿼리를 수행할 수 있을까?

SELECT 종류, 제목, 도착시간, 크기
  FROM 
     ( SELECT ROWNUM 순번, 종류, 제목, 도착시간, 크기
         FROM 
            ( SELECT /*+ INDEX_DESC(메일_내역 메일_아이디_도착시간) */
                          번호, 종류, 제목, 도착시간, 크기
                FROM 메일_내역
               WHERE 메일_ID= 'ABC'
            )
     )
 WHERE 순번 BETWEEN 1 AND 10;

ORDER BY 절을 제거하기 위해 메일_내역 테이블에 반드시 메일_아이디+도착시간 인덱스가 존재해야 한다. 또한 해당 인덱스를 아래에서부터 위로 액세스하기 위해 쿼리에 INDEX_DESC 힌트를 설정해야 할 것이다.

위와 같이 목록 쿼리를 수행 한다면 메일_아이디+도착시간 인덱스를 이용하게 되며 힌트에의해 인덱스의 아래부터 위로 액세스하게 된다.

인덱스의 아래부터 위로 액세스한다는 것은 무엇을 의미하는가? 인덱스가 메일_아이디+도착시간으로 구성되어 있으며 메일_ID 컬럼의 값이 'ABC'인 데이터를 추출하게 되므로 인덱스에서 메일_아이디가 'ABC'인 인덱스 값만 액세스하게 된다.

또한 인덱스의 아래부터 위로 액세스하게 되므로 메일_ID 컬럼의 값이 'ABC'인 데이터에 대해 자동으로 최근 도착시간 데이터부터 추출될 것이다. 이는 인덱스가 메일_아이디+도착시간으로 구성되어 있으므로 인덱스의 값이 메일_ID 컬럼으로 정렬되어 있으며 동일 메일_ID 컬럼 값에 대해서는 도착시간 컬럼으로 정렬되어 있기 때문이다.

결국, 인덱스를 이용해 ORDER BY 절을 제거할 수 있게 된다. 하지만 이것만으로는 n-Row 처리가 완성되지 않는다. ORDER BY 절을 사용하면 반드시 전체 데이터 처리가 발생하지만 ORDER BY 절을 사용하지 않는다고 반드시 n-Row 처리로 수행되는 것은 아니다.

그렇기 때문에 n-Row 처리로 수행하기 위해 ROWNUM 연산자를 사용해야 할 것이다. ROWNUM 연산자를 사용해도 되는 이유는 목록 쿼리는 페이지마다 보여주는 데이터의 건수가 정해져 있기 때문에 정해진 데이터 건수 이상을 추출할 필요가 없기 때문이다.

어차피 정해진 데이터 이상을 액세스한다면 추후 버리는 작업을 수행해야 할 것이다. 그리고 데이터가 인덱스를 이용하기 때문에 도착시간 컬럼 순으로 자동 추출되므로 전체를 모두 액세스할 필요가 없으므로 ROWNUM 연산자를 사용할 수 있게 된다.

또한 가장 안쪽의 인라인 뷰에서 도착시간 컬럼으로 정렬된 데이터가 자동으로 추출되므로 순번을 할당하기 위해 인라인 뷰를 한번 더 사용할 필요도 없게 된다.

SELECT 종류, 제목, 도착시간, 크기
  FROM 
     ( SELECT /*+ INDEX_DESC(메일_내역 메일_아이디_도착시간) */
              ROWNUM 순번, 번호, 종류, 제목, 도착시간, 크기
         FROM 메일_내역
        WHERE 메일_ID= 'ABC'
          AND ROWNUM <= 10
     )
 WHERE 순번 BETWEEN 1 AND 10;

위의 목록 쿼리에서 가장 안쪽에 존재하는 인라인 뷰는 인덱스를 통해 도착시간 컬럼을 기준으로 최근 도착시간 데이터부터 정렬된 값을 추출하게 된다.

우리의 목표는 가장 최근 데이터부터 10건의 데이터를 추출하는 것이다. 그렇기 때문에 가장 안쪽 인라인 뷰에서 가장 최근 데이터로 10건의 데이터만 추출 한다면 우리가 필요한 데이터는 모두 존재하는 것이다.

위의 인라인 뷰는 자동으로 도착시간 컬럼으로 정렬을 수행하는 인덱스를 이용하게 되므로 추출되는 데이터는 자동으로 최근 도착 시간 순으로 추출될 것이다. 그렇기 때문에 인라인 뷰에 ROWNUM 연산자를 사용할 수 있게 된다.

힌트와 ROWNUM 연산자를 사용한다면 힌트를 통해 인덱스의 아래부터 위로 액세스하게 되므로 'ABC'메일 아이디에 대 해 최근 도착시간 컬럼 순으로 데이터를 추출하게 된다.

또한 최근 도착시간 컬럼 순으로 데이터를 추출하다가 ROWNUM 조건에 의해 최근 도착시간을 가지는 데이터 중에 10건의 데이터만을 추출하게 된다. 결국, 정렬을 제거할 수 있었으며 ROWNUM 조건을 사용해 원하는 10건의 데이터만을 추출할 수 있게 된다.

기존에 1만 건의 데이터를 액세스해 정렬을 수행했던 것에 비하면 엄청난 성능 향상을 기대할 수 있을 것이다.

또한, 가장 안쪽의 인라인 뷰에서 단지 10건의 데이터만을 추출하기 때문에 바깥쪽에 있는 인라인 뷰 또한 10건의 데이터에 대해서만 수행되므로 기존에 발생했던 전체 범위를 수행하는 목록 쿼리의 네 가지 문제는 한번에 해결될 것이다.