페이지 처리를 하기에 앞서, 부분범위 처리가 가능하냐의 여부는 ROWNUM 을 어떻게 처리하느냐에 따라 결정되는 경우가 많으므로, 먼저 ROWNUM 의 효율적인 사용방법에 대해 알아야 한다.
SELECT *
FROM (
SELECT ROWNUM rnum ,
x.*
FROM (
SELECT * FROM ...
ORDER BY ...
) x
WHERE ROWNUM <= :b2
)
WHERE rnum >= :b1 ;
앞의 SQL 에서 ROWNUM <= :B2 부분은 Optimizer 에게 데이터를 어디까지 추출하고 멈춰야 하는지를 알려주는 부분이다. 즉, COUNT STOPKEY 오퍼레이션에 의해 Order By 절에 기술된 컬럼 순서대로 정렬된 데이터를 :B2 에 입력된 값 만큼 추출하고 종료한다. 예를 들어, :B2 값이 100 이라면 전체 데이터 중 정렬 순서에 따라 100 건을 추출하면 데이터 추출을 멈추게 된다.
이렇게 SQL 에서 추출될 데이터는 많지만, 일부 데이터만 빠르게 추출하기 위해 조건으로 사용할 수 있는 것이 ROWNUM 이다. 그래서 페이지 처리 시 부분범위 처리가 가능하도록 SQL 을 작성하기 위한 필수 요건 중 하나가 바로 ROWNUM 이다. 그런데 ROWNUM 은 부분범위 처리가 되도록 사용하지 않으면, 전체범위 처리로 수행되어 SQL 성능개선에 도움을 주지 못한다.
SELECT *
FROM (
SELECT ROWNUM rnum ,
x.*
FROM (
SELECT * FROM ...
ORDER BY ...
) x
)
WHERE rnum >= :b1 AND rnum <= :b2 ;
SELECT *
FROM (
SELECT ROWNUM rnum ,
x.*
FROM (
SELECT * FROM ...
ORDER BY ...
) x
)
WHERE rnum BETWEEN :b1 AND :b2 ;
SQL(1),SQL(2)의 경우 모두 전체 데이터를 조회하고, 정렬 후 전체 데이터 중 :B1 ~ :B2에 매칭되는 데이터를 추출한다. 전체 데이터 처리량이 많다면 이러한 페이지 처리는 성능문제를 일으킬 수 있다. 다만, 클라이언트 화면에 보여줄 한 페이지에 필요한 데이터만 추출하여 DB 서버에서 WAS 서버로 전송하기 때문에 네트워크 부하나 WAS 서버의 물리 메모리 사용량을 감소시킬 수 있는 장점은 있다.
지금까지 페이지 처리 시 효율적인 SQL 작성을 하기 위한 필수 요건인 ROWNUM 처리에 대해 알아 보았다. 앞에서는 이론상으로 성능을 예측하였으므로, 이제부터 테스트를 통해 ROWNUM 처리에 따른 SQL 성능차이를 알아 보도록 하자.
DROP TABLE T1 PURGE;
< T1 >
■ 생성 요건
- 테이블 데이터 건수는 1,000,000 Rows
- 컬럼 c1 은 값의 종류가 1,000,000 가지 즉, unique 성
■ 테이블 생성
CREATE TABLE t1
AS
SELECT 1000000-(LEVEL-1) c1, TO_CHAR(SYSDATE-(LEVEL-1),'yyyymmdd') c2, LEVEL c3
FROM DUAL
CONNECT BY LEVEL <= 1000000 ;
■ 각 컬럼에 인덱스 생성 및 통계정보 수집
CREATE INDEX idx_t1_01 ON t1(c2,c1);
BEGIN
dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',
TABNAME=>'T1',
ESTIMATE_PERCENT=>99,
METHOD_OPT=>'FOR ALL INDEXED COLUMNS',
GRANULARITY=>'ALL', CASCADE=>TRUE,
NO_INVALIDATE=>FALSE) ;
END;
/
이제 아래와 같은 요건을 가진 화면이 있다고 가정하고, SQL 작성 시 ROWNUM 처리를 바꿔가면서 어떻게 작성해야 효율적인 수행이 되는지 알아 보자.
[화면 설명]
추출 조건 : T1 테이블에서 C2 (날짜 데이터) 컬럼 조건에 의해 오늘 이전의 모든 데이터를화면에 출력하여야 한다는 요건이 있다고 가정 한다. 하지만 오늘 이전의 데이터를 모두 추출하고자 할 경우 데이터가 너무 많아 페이지 처리를 하였다. 이때 각 페이지에 C2, C1 컬
럼을 내림차순으로 정렬한 데이터를 50 건씩 보여줘야 한다. 이제 위 추출 조건을 만족하는 SQL 을 작성해 보도록 하자.
먼저, 오늘 이전의 모든 데이터를 추출하고 C2, C1 컬럼으로 정렬하는 SQL 을 작성하도록 하자. 작성된 SQL 은 아래와 같을 것이다.
SELECT *
FROM t1
WHERE c2 <= TO_CHAR(SYSDATE,'yyyymmdd')
ORDER BY c2 DESC, c1 DESC ;
이제 SQL 에서 추출되는 전체 데이터 중 선택된 페이지에 해당하는 50 건 만 가져오도록 ROWNUM 조건을 추가해야 할 것이다. 아래의 테스트[1] ~ 3은 동일한 데이터를 추출하는 SQL 이지만, ROWNUM을 처리하는 방법은 서로 다르다. 이제 이 3 개의 SQL 을 통해ROWNUM 처리 방법에 따라 SQL 성능이 어떻게 달라지는지 확인해 보도록 하자.
SELECT *
FROM (
SELECT ROWNUM rnum,
x.*
FROM (
SELECT
*
FROM t1
WHERE c2 <= TO_CHAR(SYSDATE,'yyyymmdd')
ORDER BY c2 DESC, c1 DESC
) x
)
WHERE rnum >= :b1 AND rnum <= :b2 ;
call cou 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.76 0.77 0 535987 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.78 0.77 0 535987 0 50
Rows Row Source Operation
------- ---------------------------------------------------
50 FILTER (cr=535987 pr=0 pw=0 time=50 us)
50 VIEW (cr=535987 pr=0 pw=0 time=48 us)
1000000 COUNT (cr=535987 pr=0 pw=0 time=44 us)
1000000 VIEW (cr=535987 pr=0 pw=0 time=42 us)
1000000 TABLE ACCESS BY INDEX ROWID T1 (cr=535987 pr=0 pw=0 time=41 us)
1000000 INDEX RANGE SCAN DESCENDING IX_T1_2 (cr=3489 pr=0 pw=0 time=33 us)
Note. Order By 절 순서와 동일한 인덱스가 존재하므로 원하는 부분까지만 읽고 처리할 것 같지만 트레이스 결과를 보면 전체 데이터를 처리한 후 50 건을 추출하였다.
SELECT *
FROM (
SELECT ROWNUM rnum ,
x.*
FROM (
SELECT /*+ index_desc(t1(c2)) */
*
FROM t1
WHERE c2 <= TO_CHAR(SYSDATE,'yyyymmdd')
ORDER BY c2 DESC, c1 DESC
) x
)
WHERE rnum BETWEEN :b1 AND :b2 ;
call cou 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.76 0.77 0 535987 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.78 0.77 0 535987 0 50
Rows Row Source Operation
------- ---------------------------------------------------
50 FILTER (cr=535987 pr=0 pw=0 time=50 us)
50 VIEW (cr=535987 pr=0 pw=0 time=48 us)
1000000 COUNT (cr=535987 pr=0 pw=0 time=44 us)
1000000 VIEW (cr=535987 pr=0 pw=0 time=42 us)
1000000 TABLE ACCESS BY INDEX ROWID T1 (cr=535987 pr=0 pw=0 time=41 us)
1000000 INDEX RANGE SCAN DESCENDING IX_T1_2 (cr=3489 pr=0 pw=0 time=33 us)
Note. 인덱스를 내림차순으로 읽으라는 힌트가 없어서 이런 일이 발생한 것이 아닌가 하여 힌트를 부여하고 ROWNUM 조건을 BETWEEN 으로 작성하여 SQL 을 수행하였으나, 결과는 테스트[1]과 동일하다. 무엇이 문제일까? 원인은 ROWNUM 조건을 잘못 작성했기 때문이다. 자세한 내용은 앞의 내용 중 "부분범위 처리가 불가능한 예"을 참조하기 바란다.
var b1 number
var b2 number
exec :b1 := 1
exec :b2 := 50 ---> 50 건
SELECT *
FROM (
SELECT ROWNUM rnum,
x.*
FROM ( SELECT /*+ INDEX_DESC(C2) */ *
FROM T1
WHERE c2 <= TO_CHAR(SYSDATE,'yyyymmdd')
ORDER BY c2 DESC, c1 DESC
) x
WHERE ROWNUM <= :b2
)
WHERE rnum >= :b1 ;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.05 0 0 0 0
Fetch 5 0.00 0.00 0 12 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.05 0 12 0 50
Rows Row Source Operation
------- ---------------------------------------------------
50 VIEW (cr=12 pr=0 pw=0 time=93 us)
50 COUNT STOPKEY (cr=12 pr=0 pw=0 time=86 us)
50 VIEW (cr=12 pr=0 pw=0 time=83 us)
50 TABLE ACCESS BY INDEX ROWID T1 (cr=12 pr=0 pw=0 time=81 us)
50 INDEX RANGE SCAN DESCENDING IX_T1_2 (cr=7 pr=0 pw=0 time=49 us)
테스트를 통해 알아본 것과 같이 ROWNUM 처리방법이 SQL 성능에 미치는 영향이 매우 크다. 그러므로 앞으로는 ROWNUM을 무조건 사용하기 보다는 부분범위 처리를 할 수 있도록 SQL 을 작성해야 할 것이다.