앞에서 소개한 페이지 처리 시 알아야 할 필수 요건을 토대로, 효율적인 페이지 처리 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 을 작성하여 어느 정도의 성능차이가 발생하는지 분석해 보도록 하자.
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 처리 부적합 (전체범위 처리가 되는 방식)
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 처리가 부적절
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 ;
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 처리가 적합 (부분범위 처리가 되는 방식)
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 ;
h6.잘못된 페이지 처리[2]의 개선 후 SQL
SELECT *
FROM (
SELECT ROWNUM rnum,
X.*
FROM (
SELET *
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 ;
SELECT *
FROM (
SELECT ROWNUM rnum ,
x.*
391
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 처리가 잘못되어 있을 때 보
이는 오퍼레이션으로 부분범위 처리가 되지 않음.
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 처리가 부적절
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 ;
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 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.00 0.00 0 0 0 0
Fetch 5 0.07 2.23 358 658 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.07 2.23 358 658 0 50
Rows Row Source Operation
---------- ---------------------------------------------------
50 VIEW (cr=658 pr=358 pw=0 time=94414 us cost=9 size=206 card=1)
■ ROWNUM 처리가 적합해야 한다
■ WHERE절과 인덱스 컬럼 조건에 따라 ORDER BY 구성을 잘하자
■ Diving Table 이여야 한다
■ 테이블 간 조인방법은 Nested Loops Join 이여야 한다
■ COUNT STOPKEY만 믿어야 한다
50 COUNT STOPKEY (cr=658 pr=358 pw=0 time=94403 us)
50 VIEW (cr=658 pr=358 pw=0 time=94370 us cost=9 size=193 card=1)
50 TABLE ACCESS BY INDEX ROWID TRM150(cr=658 pr=358 pw=0 time=94360 us ...)
122 NESTED LOOPS (cr=608 pr=302 pw=0 time=10541661 us ...)
61 NESTED LOOPS (cr=480 pr=264 pw=0 time=1770693 us ...)
136 TABLE ACCESS BY INDEX ROWID TRD100 (cr=140 pr=128 ...)
136 INDEX RANGE SCAN DESCENDING IX_TRD100_01 (cr=8 pr=3 ...)
61 TABLE ACCESS BY INDEX ROWID TRM100 (cr=340 pr=136 ...)
61 INDEX UNIQUE SCAN IX_TRM100_02(cr=279 pr=80 pw=0 time=466051 us ...)
61 INDEX UNIQUE SCAN PK_TRM150 (cr=128 pr=38 pw=0 time=295945 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 ROWNUM <= :b2
[E] PLAN 상 OPERATION
50 COUNT STOPKEY (cr=658 pr=358 pw=0 time=94403 us)
SQL 분석결과 . [A], [B], [C], [D]의 정보로 [E]를 분석
처리결과 - 화면 페이지 처리가 효율
COUNT STOPKEY . PLAN 에 SORT ORDER BY[STOPKEY] 없이 COUNT STOPKEY 만 있는 경우는, ROWNUM 처리와 ORDER BY 의 구성 등이 모두 적합하여 효율적인 페이지 처리가 되는 경우임.
[성능 문제 SQL]이 부분범위 처리가 가능한 효율적인 페이지 처리가 되도록 SQL 을 작성하는 방법에 대해서 알아 보았다. ROWNUM 처리, Where 절 조건, Order By 절, 그리고 인덱스 구성은 효율적인 페이지 처리에 꼭 필요한 필수 요건이다. 그리고 Where 절과 Order By 절을 수행하는 테이블은 Driving Table 이여야 하고, 조인은 Nested Loops Join 으로 수행되어야 한다.
여기까지 내용을 읽은 독자들은 어쩌면 페이지 처리를 매우 간단하게 생각할 수 있다. 하지만 페이지 처리의 기본과 핵심 내용을 완벽하게 이해하지 못한다면, 잘못된 페이지 처리를 SQL 에 적용하여, 엉뚱한 데이터가 출력된다거나 예기치 못한 비효율을 발생시켜 DB 서버
에 성능 문제를 일으킬 수도 있으므로 주의해야 한다. 더욱이 페이지 처리는 결코 간단하게 이해할 수 있는 내용이 아니므로, 꼼꼼하고 깊이 있는 이해를 바탕으로 SQL 을 작성해야 할 것이다.