권순용의 DB 이야기
목록 쿼리를 최적화하자. 3부. 1 6 99,999+

by axiom 목록쿼리 최적화 ROWNUM n-Row 처리 [2013.10.04]


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

지난 강의에는 전체데이터 처리 방식의 SQL을 n-Row 처리 방식으로 변경하는 방법을 확인해 봤다. 목록 쿼리를 n-Row 처리 방식으로 변경하는 것은 해당 목록 쿼리를 최적화할 수 있는 방식이다. 하지만, 우리가 사용하는 목록 쿼리의 SQL은 여러 가지 행태가 존재한다. 위와 같은 다양한 형태의 목록 쿼리를 n-Row 처리 방식으로 변경하는 것은 매우 중요하다. 이러한 방법을 실무에 적용한다면 획기적인 성능 향상을 기대할 수 있을 것이다.

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

지난 강의까지의 내용을 이해했다면 더 이상 하나의 테이블에 서 n-Row 처리 방식의 목록 쿼리를 작성하는 것은 어려운 일이 아닐 것이다.

프로젝트를 수행하다 보면 목록 쿼리를 하나의 테이블에서 조회하는 경우는 적다. 또한 메일과 같이 아이디가 WHERE 절에 제공된다면 처리 대상 집합이 많이 감소하기 때문에 성능에 유리할 수 있을 것이다.

하지만 많은 목록 쿼리에서 WHERE 조건이 존재하지 않는다. 이제부터 이러한 복잡한 목록 쿼리에 대해 n-Row 처리 방식으로 목록 쿼리를 작성하는 방법을 확인해 보자.

어떤 목록 쿼리일지라도 전체 데이터 처리 방식으로 만드는 ORDER BY 절의 제거와 n-Row 처리 방식으로 만드는 ROWNUM 조건의 사용이 가장 중요하다는 것을 명심하자.

이러한 규칙을 준수한다면 복잡한 목록 쿼리도 많은 부분에서 n-Row 처리를 수행하는 목록 쿼리로 변경할 수 있을 것이다. 이와같은 n-Row 처리의 목록 쿼리는 목록 쿼리의 성능을 향상시킬 수 있는 희망이라고 말해도 과언은 아닐 것이다.

그렇다고 모든 목록 쿼리를 n-Row 처리 방식의 목록 쿼리로 작성할 수 있는 것은 아니다. 이제부터 복잡한 목록 쿼리를 하나 하나 확인해 보자.

첫 번째로 ORDER BY 절의 컬럼이 하나의 테이블로 구성되 는 목록 쿼리를 확인해 보자.

ORDER BY 절에 하나의 컬럼이 사용된다면 또는 ORDER BY 절에 사용되는 컬럼들이 하나의 테이블에 존재한다면 조금은 손쉽게 n-Row 처리 방식의 목록 쿼리로 변경이 가능할 것이다.

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

위 SQL은 목록 쿼리 중에서도 복잡한 목록 쿼리에 포함될 수 있다. 위의 목록 쿼리 또한 전체 데이터 처리를 수행하고 있다. 따라서 우리가 목표로 하는 n-Row 처리 방식으로 수행하는 목록 쿼리로 변경해야 할 것이다.

n-Row 처리 방식으로 목록 쿼리를 변경하기 위해서는 ORDER BY 절의 사용을 제거하고 ROWNUM 조건을 효과적으로 사용해야 한다.

ORDER BY 절을 제거하기 위해서는 인덱스를 이용해야 한다. 하지만 해당 목록 쿼리는 단일 테이블을 조회하는 목록 쿼리가 아니며 2개 이상의 테이블을 조회하는 조인을 이용한 목록 쿼리이다.

조인에서 정렬을 제거하기 위해서는 조인 방식에 따라 방법이 변하게 된다. 목록 쿼리는 대부분 온라인 업무에 해당한다. 그렇기 때문에 일반적으로 온라인 업무에 유리한 조인 방식인 중첩 루프 조인(NESTED-LOOP JOIN)을 사용했다고 가정하자.

물론 일반적으로 온라인 업무에서 중첩 루프 조인(NESTED-LOOP JOIN)을 사용하지만 경우에 따라서 해시 조인(HASH JOIN)을 사용할 수도 있다.

중첩 루프 조인의 경우 먼저 수행되는 테이블인 DRIVING 테이블의 인덱스에 의해 정렬된 데이터가 추출된다.

위의 경우는 민원요청 테이블의 등록일자 컬럼에 대해 정렬된 데이터를 추출해야 하므로 민원요청 테이블이 먼저 엑세스되어야 한다.

또한, 민원요청 테이블의 등록일자 컬럼에 인덱스가 존재해야하며 민원요청 테이블을 엑세스하는 경우 해당 인덱스를 이용 해야 등록일자 컬럼으로 정렬된 데이터가 추출될 것이다.

그래야만 민원요청 테이블의 등록일자 컬럼으로 정렬된 데이터를 ORDER BY 절을 사용하지 않고 추출할 수 있을 것이다.

또한 성능을 보장받기 위해 중첩 루프 조인에서는 INNER 테이블의 인덱스가 중요하다. 그러므로 INNER 테이블로 수행되는 민원인 테이블은 조인 조건인 ID 컬럼에 인덱스가 존재해야 한다.

이와 같은 방법으로 ORDER BY 절을 제거한다면 ROWNUM 조건을 사용할 수 있게 될 것이다.

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

위와 같이 조인에서 정렬을 제거하는 방법을 통해 ORDER BY 절을 제거하고 n-Row 처리로 수행될 수 있도록 ROWNUM 조건을 사용함으로써 해당 목록 쿼리는 n-Row 처리로 수행되는 목록 쿼리가 된다.

조인을 이용하는 목록 쿼리도 이와 같이 많은 고민과 생각을 한다면 ORDER BY 절을 제거할 수 있다. 조인을 이용하는 목록 쿼리 또한 이와 같이 ORDER BY 절을 제거한다면 더 좋은 성능을 기대할 수 있을 것이다.

다음 강의에서는 또 다른 형태의 SQL에 대해 n-Row 처리의 목록 쿼리로 변경하는 방법을 확인해 보자.

- 강좌 URL : http://www.gurubee.net/lecture/2631

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 아발란체 [2013.10.04 15:42:03]

좋은 강좌 감사합니다.


by 오라오라 [2013.12.26 17:31:38]

좋은강좌 큰도움되었습니다. 감사합니다.

by 토토땅 [2017.06.20 16:58:57]

정말 최고입니다! 감사합니다.


by 광이 [2018.10.24 13:31:59]

감사합니다


by JCouday [2020.04.12 12:37:06]

마지막 쿼리문에서 인라인문에  "AND ROWNUM <= 10"가 있는데 

주쿼리에서 "WHERE 순번 BETWEEN 1 AND 10;" 조건을 계속 가지고 있는 이유는 뭔가요? 이미 rownum 10개 이하만 나오는 거에서 또다시 같은 조건을 거는거 같아서 불필요한 작업 아닌가요?


by 마농 [2020.04.13 08:17:09]

페이지 번호에 따라 숫자가 바뀌는 쿼리입니다.
1 페이지 에서는 불필요해 보일 수도 있으나. (ROWNUM <= 10, 순번 BETWEEN 1 AND 10)
2 페이지 에서는 빠지면 이상해 지죠. (ROWNUM <= 20, 순번 BETWEEN 11 AND 20)

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입