SQL 튜닝의 시작 (2013년)
기본적인 형태의 PAGINATION을 익히자 0 0 53,333

by 구루비스터디 PAGINATION [2018.07.14]


기본적인 형태의 PAGINATION을 익히자

  • 앞에서 소개한 페이지 처리 시 알아야 할 필수 요건을 토대로, 효율적인 페이지 처리 SQL을 작성하는 과정을 같이 진행해 보자.
  • 이 과정을 통해 페이지 처리에 대한 보다 깊은 이해를 할 수 있는 기회가 되길 희망한다.
  • 아래 성능 문제 SQL은 페이지 처리가 되어 있지 않은 온라인 화면이며, 페이지 처리는 WEB 서버에서 이루어 진다고 가정하자.
  • 이런 SQL 은 많은 비효율을 가지고 있으므로, 효율적인 페이지 처리가 가능한 SQL 로 완성해 가도록 하자.


성능 문제 SQL

SELECT ... 생략
  FROM trd100 a ,
       trm100 b ,
       trm150 c
  WHERE a.acpt_no = b.acpt_no
    AND a.acpt_no = c.acpt_no
    AND b.styl_cd = 'AC01'
    AND a.proc_stus IN ( 'D' , 'E' )
    AND a.brof_cd LIKE TRIM( :b0 ) || '%'
    AND a.acpt_dt || a.acpt_time < :b1
  ORDER BY a.acpt_dt DESC ,
           a.acpt_time DESC ;


call    count   cpu     elapsed    disk       query      current     rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1     0.00       0.00        0        0          0       0
Execute      1     0.00       0.01        0        0          0       0
Fetch       41     0.90      26.17     4718     8666          0     599
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total       43     0.90      26.18     4718     8666          0     599
Rows        Row Source Operation
---------- ---------------------------------------------------
599         SORT ORDER BY (cr=8666 pr=4718 pw=0 time=26173450 us ...)
599         TABLE ACCESS BY INDEX ROWID TRM100 (cr=8666 pr=4718 ...)
1199        NESTED LOOPS (cr=8067 pr=4141 pw=0 time=33177311 us ...)
599         NESTED LOOPS (cr=6864 pr=3678 pw=0 time=42743190 us ...)
2057        TABLE ACCESS BY INDEX ROWID TRD100 (cr=2143 pr=2050 ...)
2057        INDEX RANGE SCAN IX_TRD100_01 (cr=163 pr=162 ...)
599         TABLE ACCESS BY INDEX ROWID TRM150 (cr=4721 pr=1628 ...)
599         INDEX UNIQUE SCAN PK_TRM150 (cr=4122 pr=1059 ...)
599         INDEX UNIQUE SCAN IX_TRM100_02 (cr=1203 pr=463 ...)


[A] WHERE 조회 조건
AND a.proc_stus IN ( 'D' , 'E' )
AND a.brof_cd LIKE TRIM( :b0 ) || '%'


[B] 인덱스 구성정보
INDEX_NAME    COLUMN LIST
------------ ---------------------------------------
ix_trd100_01  brof_cd, proc_stus, acpt_dt, acpt_time


[C] ORDER BY (정렬 기준)
ORDER BY a.acpt_dt DESC,
a.acpt_time DESC

[D] ROWNUM 처리 . 없음.

[E] PLAN 상 OPERATION
599 SORT ORDER BY (cr=8666 pr=4718 pw=0 time=26173450 us ...)



  • SQL 분석결과 . [A], [B], [C], [D]의 정보로 [E]를 분석
  • 처리결과 - 페이지 처리가 되지 않아 전체 데이터 추출하여 비효율 발생


  • 앞의 [성능 문제 SQL]은 전체 데이터를 모두 추출하는 온라인 화면의 SQL 이며, DB 서버에서 추출된 599 건을 WAS 서버로 전송한다.
  • WAS 서버는 전송받은 데이터 중에서 클라이언트 화면에 필요한 데이터 건수만큼 추출해 클라이언트에 보내게 된다.
  • DB 서버에서 효율 적인 페이지 처리 (부분범위 처리)로 수행되지 않지만, 실제 클라이언트 화면에서는 페이지 처리가 된 상태로 데이터를 보여주는 것이다.
  • 그렇다면 [성능 문제 SQL]이 효율적인 페이지 처리가 가능하도록 작성한 경우와 그렇지 못한 경우에 대한 SQL 을 작성하여 어느 정도의 성능차이가 발생하는지 분석해 보도록 하자.


잘못된 페이지 처리(1)
  • ROWNUM 처리와 ORDER BY 절을 잘못 사용한 경우 [실행계획: SORT ORDER BY -> COUNT]


SELECT *
  FROM (
        SELECT ROWNUM rnum , x.*
          FROM ( SELECT /*+ LEADING(A) USE_NL(A B C) INDEX_DESC(A IX_TRD100_01) */
                        ... 생략
                  FROM trd100 a, trm100 b, trm150 c
                WHERE a.acpt_no = b.acpt_no
                  AND a.acpt_no = c.acpt_no
                  AND b.styl_cd = 'AC01'
                  AND a.proc_stus IN ( 'D' , 'E' )
                  AND a.brof_cd LIKE TRIM( :b0 ) || '%'
                  AND a.acpt_dt || a.acpt_time < :b1
               ORDER BY a.acpt_dt DESC , a.acpt_time DESC
               ) x
      )
 WHERE rnum <= :b2 and rnum >= :b3 ;


call     count cpu         elapsed      disk      query     current  rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1    0.00        0.00         0          0          0    0
Execute      1    0.00        0.00         0          0          0    0
Fetch        5    0.90       27.90      4672       8665          0   50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        7    0.90       27.91      4672       8665          0   50
Rows        Row Source Operation
---------- ---------------------------------------------------
50          FILTER (cr=8665 pr=4672 pw=0 time=27904482 us)
50          VIEW (cr=8665 pr=4672 pw=0 time=27904479 us ...)
599         COUNT (cr=8665 pr=4672 pw=0 time=27905514 us)
599         VIEW (cr=8665 pr=4672 pw=0 time=27905065 us ...)
599         SORT ORDER BY (cr=8665 pr=4672 pw=0 time=27904910 us ...)
599         TABLE ACCESS BY INDEX ROWID TRM100 (cr=8665 pr=4672 ...)
1199        NESTED LOOPS (cr=8066 pr=4100 pw=0 time=4626814 us ...)
599         NESTED LOOPS (cr=6863 pr=3635 pw=0 time=13468562 us ...)
2057        TABLE ACCESS BY INDEX ROWID TRD100 (cr=2142 pr=1905 ...)
2057        INDEX RANGE SCAN DESCENDING IX_TRD100_01 (cr=163 pr=1 ...)
599         TABLE ACCESS BY INDEX ROWID TRM150 (cr=4721 pr=1730 ...)
599         INDEX UNIQUE SCAN PK_TRM150 (cr=4122 pr=1166 ...)
599         INDEX UNIQUE SCAN IX_TRM100_02 (cr=1203 pr=465 ...)


[A] WHERE 조회 조건
AND a.proc_stus IN ( 'D' , 'E' )
AND a.brof_cd LIKE TRIM( :b0 ) || '%'


[B] 인덱스 구성정보
INDEX_NAME     COLUMN LIST
------------- --------------------------------------
ix_trd100_01   brof_cd, proc_stus, acpt_dt, acpt_time

[C] ORDER BY (정렬 기준)
ORDER BY a.acpt_dt DESC,
a.acpt_time DESC

[D] ROWNUM 처리
rnum <= :b2 and rnum >= :b3

[E] PLAN 상 OPERATION
599 COUNT (cr=8665 pr=4672 pw=0 time=27905514 us)
599 VIEW (cr=8665 pr=4672 pw=0 time=27905065 us ...)
599 SORT ORDER BY (cr=8665 pr=4672 pw=0 time=27904910 us ...)



  • SQL 분석결과 . [A], [B], [C], [D]의 정보로 E를 분석
  • 처리결과 - 페이지 처리가 비효율 (아래의 PLAN 의 오퍼레이션으로 판단)
  • SORT ORDER BY ORDER BY 절의 순서가 인덱스 컬럼 순서에 부적합
  • COUNT ROWNUM 처리 부적합 (전체범위 처리가 되는 방식)


잘못된 페이지 처리[1]의 개선 전 SQL

  SELECT *
   FROM (
          SELECT ROWNUM rnum ,
                  x.*
          FROM (
                SELECT * FROM ...
                 ORDER BY a.acpt_dt DESC,
                         a.acpt_time DESC ---> 문제점.Order By 절과 인덱스 구성순서와 다름
                ) x
        )
   WHERE rnum <= :b2 AND rnum >= :b3 ; ---> 문제점.ROWNUM 처리가 부적절



잘못된 페이지 처리[1]의 개선 후 SQL

  SELECT *
    FROM (
            SELECT ROWNUM rnum ,
                    x.*
              FROM (
                    SELECT * FROM ...
                     ORDER BY a.brof_cd DESC,
                              a.proc_stus DESC,
                              a.acpt_dt DESC,
                              a.acpt_time DESC ---> 개선내용.Order By 절과 인덱스 구성순서와 맞춤
                  ) x
             WHERE ROWNUM <= :b2 ---> 개선내용.부분범위 처리가 되도록 ROWNUM 처리 변경
        )
 WHERE rnum >= :b1 ;



잘못된 페이지 처리(2)
  • ROWNUM 처리는 효율적이나 ORDER BY를 잘못 사용한 경우 (실행계획 : SORT ORDER BY STOPKEY -> COUNT STOPKEY)

  SELECT *
    FROM (
           SELECT ROWNUM rnum , x.*
             FROM (
                    SELECT /*+ LEADING(A) USE_NL(A B C) INDEX_DESC(A IX_TRD100_01) */
                           ... 생략
                      FROM trd100 a ,
                           trm100 b ,
                           trm150 c
                     WHERE a.acpt_no = b.acpt_no
                       AND a.acpt_no = c.acpt_no
                       AND b.styl_cd = 'AC01'
                       AND a.proc_stus IN ( 'D' , 'E' )
                       AND a.brof_cd LIKE TRIM( :b0 ) || '%'
                       AND a.acpt_dt || a.acpt_time < :b1
                     ORDER BY a.acpt_dt DESC ,
                              a.acpt_time DESC
                  ) x
             WHERE ROWNUM <= :b2
         )
   WHERE rnum >= :b3 ;

call     count     cpu    elapsed       disk      query     current   rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1     0.00       0.00          0          0          0    0
Execute      1     0.01       0.00          0          0          0    0
Fetch        5     0.97      31.65       4949       8665          0   50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        7     0.98      31.66       4949      8665           0   50

Rows        Row Source Operation
---------- ---------------------------------------------------
50         VIEW (cr=8665 pr=4949 pw=0 time=31655963 us ...)
50         COUNT STOPKEY (cr=8665 pr=4949 pw=0 time=31655952 us)
50         VIEW (cr=8665 pr=4949 pw=0 time=31655949 us ...)
50         SORT ORDER BY STOPKEY (cr=8665 pr=4949 pw=0 time=31655944 us ...)
599        TABLE ACCESS BY INDEX ROWID TRM100 (cr=8665 pr=4949 ...)
1199       NESTED LOOPS (cr=8066 pr=4373 pw=0 time=10498695 us ...)
599        NESTED LOOPS (cr=6863 pr=3885 pw=0 time=20250270 us ...)
2057       TABLE ACCESS BY INDEX ROWID TRD100 (cr=2142 pr=2108 ...)
2057       INDEX RANGE SCAN DESCENDING IX_TRD100_01 (cr=163 pr=162 ...)
599        TABLE ACCESS BY INDEX ROWID TRM150 (cr=4721 pr=1777 ...)
599        INDEX UNIQUE SCAN PK_TRM150 (cr=4122 pr=1207 ...)
599        INDEX UNIQUE SCAN IX_TRM100_02 (cr=1203 pr=488 ...)


[A] WHERE 조회 조건
AND a.proc_stus IN ( 'D' , 'E' )
389
AND a.brof_cd LIKE TRIM( :b0 ) || '%'


[B] 인덱스 구성정보
INDEX_NAME    COLUMN LIST
------------ ---------------------------------------
ix_trd100_01 brof_cd, proc_stus, acpt_dt, acpt_time


[C] ORDER BY (정렬 기준)
ORDER BY a.acpt_dt DESC,
a.acpt_time DESC


[D] ROWNUM 처리
WHERE ROWNUM <= :b2
[E] PLAN 상 OPERATION
50 COUNT STOPKEY (cr=8665 pr=4949 pw=0 time=31655952 us)
50 VIEW (cr=8665 pr=4949 pw=0 time=31655949 us ...)
50 SORT ORDER BY STOPKEY (cr=8665 pr=4949 pw=0 time=31655944 us ...)
----------------------------------------------------------------------------------------------



  • SQL 분석결과 . [A], [B], [C], [D]의 정보로 [E]를 분석
  • 처리결과 - 페이지 처리가 비효율 (아래의 PLAN 의 오퍼레이션으로 판단)
  • SORT ORDER BY STOPKEY ORDER BY 절의 순서가 인덱스 컬럼 순서에 부적합
  • COUNT STOPKEY ROWNUM 처리가 적합 (부분범위 처리가 되는 방식)


잘못된 페이지 처리[2]의 개선 전 SQL

  SELECT *
    FROM ( SELECT ROWNUM rnum,
                  X.*
             FROM (
                    SELECT * from ...
                     ORDER BY a.acpt_dt desc,
                              a.acpt_time desc ---> 문제점.Order By 절과 인덱스 구성순서와 다름.
                  ) x
            WHERE rownum <= :b2
         )
   WHERE rnum >= :b1 ;


잘못된 페이지 처리[2]의 개선 후 SQL

SELECT *
  FROM (
       SELECT ROWNUM rnum,
              X.*
         FROM (
              SELECT *
                FROM ...
               ORDER BY a.brof_cd desc,
                        a.proc_stus,
                        a.acpt_dt desc,
                        a.acpt_time desc --> 개선내용.Order By 절과 인덱스 구성순서와 맞춤
             ) x
        WHERE ROWNUM <= :b2
       )
  WHERE rnum >= :b1 ;



잘못된 페이지 처리(3)
  • ROWNUM 처리가 잘못된 경우 (실행 계획 : COUNT [SORT ORDER BY(X)] )

  SELECT *
   FROM (
          SELECT ROWNUM rnum , x.*
            FROM (
                  SELECT /*+ LEADING(A) USE_NL(A B C) INDEX_DESC(A IX_TRD100_01) */
                          ... 생략
                    FROM trd100 a ,
                         trm100 b ,
                         trm150 c
                   WHERE a.acpt_no = b.acpt_no
                     AND a.acpt_no = c.acpt_no
                     AND b.styl_cd = 'AC01'
                     AND a.proc_stus IN ( 'D' , 'E' )
                     AND a.brof_cd LIKE TRIM( :b0 ) || '%'
                     AND a.acpt_dt || a.acpt_time < :b1
                   ORDER BY a.brof_cd DESC ,
                            a.proc_stus DESC ,
                            a.acpt_dt DESC ,
                            a.acpt_time DESC
                ) x
        )
  WHERE rnum <= :b2 AND rnum >= :b3 ;

call     count     cpu    elapsed       disk       query    current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1     0.01       0.00          0         0         0    0
Execute      1     0.00       0.00          0         0         0    0
Fetch        5     0.16       3.95        538      8677         0   50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        7     0.17       3.96        538      8677         0   50

Rows       Row Source Operation
---------- ---------------------------------------------------
50         FILTER (cr=8677 pr=538 pw=0 time=1957 us)
50         VIEW (cr=8677 pr=538 pw=0 time=1955 us cost=9 size=159 card=1)
599        COUNT (cr=8677 pr=538 pw=0 time=3261348 us)
599        VIEW (cr=8677 pr=538 pw=0 time=3260896 us cost=9 size=146 card=1)
599        TABLE ACCESS BY INDEX ROWID TRM150 (cr=8677 pr=538 ...)
1199       NESTED LOOPS (cr=8078 pr=538 pw=0 time=227759 us ...)
599        NESTED LOOPS (cr=6874 pr=530 pw=0 time=4307258 us ...)
2057       TABLE ACCESS BY INDEX ROWID TRD100 (cr=2147 pr=10 ...)
2057       INDEX RANGE SCAN DESCENDING IX_TRD100_01 (cr=167 pr=0 ...)
599        TABLE ACCESS BY INDEX ROWID TRM100 (cr=4727 pr=520 ...)
599        INDEX UNIQUE SCAN IX_TRM100_02 (cr=4128 pr=7 pw=0 time=56539 us ...)
599        INDEX UNIQUE SCAN PK_TRM150 (cr=1204 pr=8 pw=0 time=53068 us ...)


[A] WHERE 조회 조건
AND a.proc_stus IN ( 'D' , 'E' )
AND a.brof_cd LIKE TRIM( :b0 ) || '%'


[B] 인덱스 구성정보
INDEX_NAME   COLUMN LIST
------------ ---------------------------------------
ix_trd100_01 brof_cd, proc_stus, acpt_dt, acpt_time


[C] ORDER BY (정렬 기준)
ORDER BY a.brof_cd DESC,
a.proc_stus DESC,
a.acpt_dt DESC,
a.acpt_time DESC


[D] ROWNUM 처리
WHERE rnum <= :b2 and rnum >= :b3


[E] PLAN 상 OPERATION
599 COUNT (cr=8677 pr=538 pw=0 time=3261348 us)


  • SQL 분석결과 . [A], [B], [C], [D]의 정보로 [E]를 분석
  • 처리결과 - 페이지 처리가 비효율 (아래의 PLAN 의 오퍼레이션으로 판단)


  • COUNT PLAN 에 SORT ORDER BY[STOPKEY] 없이 COUNT 만 있는 경우는, ORDER BY 절의 컬럼 순서와 인덱스 구성정보는 효율적이나, ROWNUM 처리가 잘못되어 있을 때 보이는 오퍼레이션으로 부분범위 처리가 되지 않음.


잘못된 페이지 처리[3]의 개선 전 SQL

  SELECT *
  FROM (
        SELECT ROWNUM rnum,
               X.*
          FROM (
               SELECT * FROM ...
                ORDER BY a.brof_cd desc,
                         a.proc_stus desc,
                         a.acpt_dt desc,
                         a.acpt_time desc
              ) x
         )
  WHERE rnum <= :b2 AND rnum >= :b3 ; ---> 문제점.ROWNUM 처리가 부적절


잘못된 페이지 처리[3]의 개선 후 SQL

  SELECT *
    FROM (
          SELECT ROWNUM rnum,
                 X.*
            FROM (
                  SELECT * FROM ...
                   ORDER BY a.brof_cd desc,
                            a.proc_stus,
                            a.acpt_dt desc,
                            a.acpt_time desc
                  ) x
            WHERE ROWNUM <= :b2 ---> 개선내용.부분범위 처리가 되도록 ROWNUM 처리 변경
        )
  WHERE rnum >= :b3 ;


  • [성능 문제 SQL]이 부분범위 처리가 가능한 효율적인 페이지 처리가 되도록 SQL 을 작성하는 방법에 대해서 알아 보았다.
  • ROWNUM 처리, Where 절 조건, Order By 절, 그리고 인덱스 구성은 효율적인 페이지 처리에 꼭 필요한 필수 요건이다.
  • 그리고 Where 절과 Order By 절을 수행하는 테이블은 Driving Table 이여야 하고, 조인은 Nested Loops Join 으로 수행되어야 한다.


  • 여기까지 내용을 읽은 독자들은 어쩌면 페이지 처리를 매우 간단하게 생각할 수 있다.
  • 하지만 페이지 처리의 기본과 핵심 내용을 완벽하게 이해하지 못한다면, 잘못된 페이지 처리를 SQL 에 적용하여, 엉뚱한 데이터가 출력된다거나 예기치 못한 비효율을 발생시켜 DB 서버에 성능 문제를 일으킬 수도 있으므로 주의해야 한다.
  • 더욱이 페이지 처리는 결코 간단하게 이해할 수 있는 내용이 아니므로, 꼼꼼하고 깊이 있는 이해를 바탕으로 SQL 을 작성해야 할 것이다.
"데이터베이스 스터디모임" 에서 2013년에 "SQL튜닝의시작 " 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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