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

지금 이 순간에도 많은 사이트에서는 목록 쿼리를 사용하고 있을 것이다. 웹 환경에 게시판 형태의 화면을 만들기 위해서는 목록쿼리를 사용하지 않을 수 없으며 그렇기 때문에 목록 쿼리의 사용 빈도는 매우 높게 된다. 이와 같은 목록 쿼리를 최적화하는 것 만으로도 많은 시스템들은 최적화될 수 있을 것이다.

다양한 형태의 목록 쿼리를 n-Row 처리 방식으로 변경하는 것은 매우 중요하다. 이러한 방법을 실무에 적용한다면 획기적인 성능 향상을 기대할 수 있을 것이다.

조인을 이용한 목록 쿼리도 n-Row 처리로 해결해야 한다

조인을 이용한 목록 쿼리에서 ORDER BY 절에 서로 다른 테이블의 컬럼으로 정렬을 수행해야 하는 경우를 확인해 보자.

SELECT 등록일자, 민원인_성명, 민원연락처, 민원요약내용, 해결여부, 처리부서
  FROM 
     ( 
       SELECT ROWNUM 순번, 등록일자, 민원인_성명, 민원연락처,
                 민원요약내용, 해결여부, 처리부서
         FROM 
            ( 
              SELECT AA.등록일자, BB.민원인_성명, BB.민원인연락처,
                     AA.민원요약내용, AA.해결여부, AA.처리부서
                FROM 민원요청 AA, 민원인 BB
               WHERE AA.ID = BB.ID
                 AND AA.등록일자 > '20070101'
                 AND BB.민원인_성명 LIKE '김%'
               ORDER BY AA.등록일자 DESC, BB.민원인_성명
            )
     )
 WHERE 순번 BETWEEN 1 AND 10;

위의 목록 쿼리는 민원요청 테이블에 존재하는 등록일자 컬럼과 민원인 테이블에 존재하는 민원인_성명 컬럼으로 정렬을 수행하는 쿼리다.

결국, 추출되는 데이터는 등록일자 컬럼의 값으로 정렬이 수행되며 동일 등록일자 컬럼의 값에 대해서는 민원인_성명 컬럼의 값으로 정렬을 수행해야 한다.

앞서 언급했듯이 중첩 루프 조인이라면 먼저 엑세스되는 테이블의 인덱스에 의해 정렬이 수행된다. 따라서 민원요청 테이블의 등록일자 인덱스를 이용해 민원요청 테이블이 먼저 엑세스된다면 등록일자 컬럼의 값으로 정렬된 데이터를 ORDER BY 절의 사용 없이 추출할 수 있다.

그렇다면 중첩 루프 조인에서 위의 예제처럼 뒤에 엑세스되는 INNER 테이블인 민원인 테이블의 민원인_성명 컬럼에 대해 인덱스만을 이용해 정렬된 데이터가 추출될 수 있는가?

물론 민원요청 테이블의 등록일자 컬럼으로 정렬된 데이터에 대해 동일한 등록일자 값일 경우 민원인_성명 컬럼의 값으로 정렬을 수행해야 한다.

인덱스를 이용해 민원인_성명 컬럼으로는 정렬이 수행되지 않게 된다.

하지만 등록일자 컬럼의 값이 UNIQUE한 값이라면 하나의 등록일자 컬럼의 값은 ID 값을 민원인 테이블에 제공하게 되고 해당 민원인 테이블은 ID+민원인_성명 인덱스를 이용 한다면 제한적으로 정렬된 값을 추출할 수 있다.

이번에는 ORDER BY 절에 민원인_성명 컬럼 대신 조인 조건인 ID 컬럼을 넣으면 어떤 현상이 발생하는지 확인해 보자.

SELECT 등록일자, 민원인_성명, 민원연락처, 민원요약내용, 해결여부, 처리부서
  FROM 
     ( 
       SELECT ROWNUM 순번, 등록일자, 민원인_성명, 민원연락처,
                 민원요약내용, 해결여부, 처리부서
         FROM 
            ( 
              SELECT AA.등록일자, BB.민원인_성명, BB.민원인연락처,
                     AA.민원요약내용, AA.해결여부, AA.처리부서
                FROM 민원요청 AA, 민원인 BB
               WHERE AA.ID = BB.ID
                 AND AA.등록일자 > '20070101'
                 AND BB.민원인_성명 LIKE '김%'
               ORDER BY AA.등록일자 DESC, BB.ID
            )
     ) 
 WHERE 순번 BETWEEN 1 AND 10;

ORDER BY 절에서 동일한 등록일자 컬럼에 대해 민원인 테이블의 ID 컬럼으로 정렬을 수행한다면 인덱스를 이용해 정렬을 수행할 수 있게 된다.

민원요청 테이블이 중첩 루프 조인의 DRIVING 테이블이며 민원요청 테이블에는 등록일자+ID 인덱스가 존재한다고 가정하자.

DRIVING 테이블의 인덱스가 등록일자+ID로 구성되어 있으므로 해당 결과 값은 등록일자 컬럼으로 정렬되어 추출된다. 동일 등록일자 값에 대해서는 ID 컬럼으로 정렬된다.

따라서 동일 등록일자 값은 ID 값의 순서대로 INNER 테이블로 제공되므로 동일 등록일자 값에 대해 ID 컬럼의 값으로 정렬된 값이 추출될 것이다.

SELECT 등록일자, 민원인_성명, 민원연락처, 민원요약내용, 해결여부, 처리부서
  FROM 
     ( 
       SELECT /*+ INDEX_DESC(AA, 등록일자_ID_IDX) */
              ROWNUM 순번, AA.등록일자, BB.민원인_성명, BB.민원인연락처,
              AA.민원요약내용, AA.해결여부, AA.처리부서
         FROM 민원요청 AA, 민원인 BB
        WHERE AA.ID = BB.ID
          AND AA.등록일자 > '20070101'
          AND BB.민원인_성명 LIKE '김%'
          AND ROWNUM <= 10
     )
 WHERE 순번 BETWEEN 1 AND 10;

결국 위와 같이 목록 쿼리를 수행해 등록일자 컬럼으로 정렬된 결과 데이터에 대해 동일한 값을 가지는 등록일자 컬럼에 대해서는 ID 컬럼으로 정렬된 값이 추출된다.

위의 목록 쿼리와 같이 ORDER BY 절을 제거하고 ROWNUM 조건을 추가함으로써 전체 데이터 처리의 목록 쿼리를 n-Row 처리의 목록 쿼리로 변경할 수 있게 된다.

중첩 루프 조인은 먼저 엑세스되는 DRIVING 테이블의 인덱스 구성에 의해 인덱스의 첫 번째 컬럼에 의해 정렬된 값이 추출된다.

물론 DRIVING 테이블이 사용하는 인덱스의 첫 번째 컬럼이 점 조건이라면 두 번째 컬럼에 의해서도 정렬된 데이터가 추출될 수 있다.