본 프로젝트를 진행하며 비효율적으로 구성된 많은 페이징 처리 SQL들이 다양한 업무에서 발견되었다. 페이징 처리 SQL들이 잘못 구성된 이유는 크게 rownum 및 부분 범위 처리의 이해 부족 두 가지로 생각된다.

문제는 이러한 SQL들이 시스템 오픈 이후 일정 시점까지는 데이터양이 크지 않아 발견이 되지 않으므로 차후에 성능지연을 발견하더라도 ‘데이터가 증가하여 어쩔 수 없는 현상이다’라고 많은 개발자나 업무 담당자가 생각한다는 것이다.

이러한 이유로 잘못된 페이징 처리 방식을 보유한 SQL이 외산 DBMS보다 성능이 나오지 않을 경우 1차 원인은 SQL 구성의 잘못임에도 불구하고 오로지 티베로 때문에 성능지연이 발생한다고 생각하기도 하였다(물론 외산 DBMS와 성능이 유사하거나 나은 경우는 페이징 처리에 문제가 없다고 판단하고 있었다). 그렇다면 어떻게 해야 올바른 페이징 처리를 구현할 수 있는지 확인해보자.

먼저 페이징 처리의 올바른 구현을 위해선 rownum의 정확한 이해가 필요하다. 그러기 위해 Tibero SQL Guide에서 제공하고 있는 rownum의 정의를 살펴보자.

ROWNUM(SELECT 문장의 실행결과로 나타나는 로우에 대하여 순서대로 번호를 부여한다).

-- ROWNUM이 할당되는 순서
① 질의를 수행한다.
② 질의 결과로 로우가 생성된다.
③ 로우를 반환하기 직전에 그 로우에 ROWNUM이 할당된다. Tibero RDBMS는 내부적으로
ROWNUM 카운터를 가지고 있으며, 카운터 값을 질의 결과의 로우에 할당한다.
④ ROWNUM을 할당받은 로우에 ROWNUM에 대한 조건식을 적용한다.
⑤ 조건식을 만족하면 할당된 ROWNUM이 확정되고, 내부의 ROWNUM 카운터의 값이 1로 증가한다.
⑥ 조건식을 만족하지 않으면 그 로우는 버려지고, 내부의 ROWNUM 카운터의 값은 증가하지 않는다.

-- 조회 결과 1)
SQL> SELECT rownum, emp_no FROM employee; 
ROWNUM EMP_NO
---------- --------
1 20081120
2 20083311
3 20083313 

-- 조회 결과 2) 아래의 SQL 문장은 10개의 로우만을 반환하는 예제이다.
SELECT * FROM EMP WHERE ROWNUM <= 10;


-- 조회 결과 3) 아래의 SQL 문장은 실행할 때마다 다른 결과를 얻는다.
SELECT * FROM EMP WHERE ROWNUM <= 10 ORDER BY EMPNO;


-- 조회 결과 4) 위의 질의를 아래와 같이 변환하면 ORDER BY 절을 먼저 처리하게 되므로 
-- 항상 같은 결과를 얻을 수 있다.

SELECT * 
  FROM 
     ( 
       SELECT * FROM EMP ORDER BY EMPNO
     ) 
 WHERE ROWNUM <= 10;
 
-- 조회 결과 5) 아래의 SQL 문장은 하나의 로우도 반환하지 않는다. 
-- 이유는 ROWNUM 값이 확정되기 전에 ROWNUM에 대한 조건식이 수행되기 때문이다.

위의 SELECT문의 결과는 첫 번째 로우가 ROWNUM = 1이기 때문에 조건식을 만족하지 않는다
(조건식을 만족하지 않으면 ROWNUM 카운터의 값은 변하지 않는다). 
따라서 두 번째 결과 로우도 ROWNUM = 1이므로 반환되지 않는다.

SELECT * FROM EMP WHERE ROWNUM > 1;

조회 결과 2)에서 보는 바와 같이 where rownum <= 10을 사용하면 필요한 ROWS 만큼만 반환하는데 중요한 것은 이때 EMP의 모든 데이터를 읽는 것이 아닌 필요한 ROWS만 읽고(count stop, 부분범위 처리) 반환 한다는 것이다.

또한 일반적인 페이징의 경우 게시판 ID 혹은 최근 일자 등의 기준으로 정렬된 이후에 필요한 ROWS만큼 출력되는 형태이므로 조회 결과 4)와 같이 order by를 포함한 서브 쿼리를 인라인 뷰 형태로 작성^주3) 하고 해당 뷰 조건에 where rownum <= 10을 사용하는 것이 올바르게 작성된 페이징 SQL의 기본적인 형태가 된다.

그리고 ORDER BY 기준 컬럼(조건이 있다면 조건과 함께)에는 알맞은 인덱스가 할당되어 있어야 해당 인덱스를 활용하여 최소한의 스캔(부분범위 처리)만 하고 필요한 페이지에 해당하는 ROWS만 반환할 수 있다

|주3| order by와 rownum이 함께 사용되면 rownum의 값은 order by가 수행되기 이전에 할당되므로 정렬을 보장할 수 없어(조회 결과 3) 반드시 인라인 뷰 형태로 작성해야 한다

조건을 요약하면 다음 두 가지와 같을 것이며 참고 4-8과 같은 형태로 작성이 가능 할 것이다.

1. order by 및 where 조건을 고려한 알맞은 서브 쿼리를 인라인 뷰 형태로 구성할 것

2. 인라인 뷰의 바깥 조건에 where rownum <= rows 조건을 명시하여 필요한 rows만 스캔하고 반환하도록 할 것

-- 예시1)

SELECT *
FROM (
 SELECT ROWNUM RN, 컬럼1, 컬럼2
 FROM (
 ---인라인 뷰 구성( [컬럼1+컬럼2] 인덱스가 존재해야 최적의 성능 )
 SELECT 컬럼1, 컬럼2
 FROM 기준테이블
 WHERE 컬럼1 = :조건1
 AND 컬럼2 = :조건2
 ORDER BY 컬럼1, 컬럼2
 )
 WHERE ROWNUM <= :페이지 * :한페이지출력수
 )
WHERE TRUNC((RN-1)/:한 페이지출력 수)=:페이지-1


 -- 예시2)
SELECT RN, 컬럼1, 컬럼2
FROM (
 ---인라인 뷰 구성 ( [컬럼1+컬럼2] 인덱스가 존재해야 최적의 성능 )
 SELECT 컬럼1, 컬럼2, ROWNUM RN
 FROM 기준테이블
 WHERE 컬럼1 = :조건1
 AND 컬럼2 = :조건2
 ORDER BY 컬럼1, 컬럼2
 )
WHERE RN <= : 페이지 * : 한 페이지 출력 수
AND TRUNC((RN-1)/:한페이지출력수)=:페이지-1

소속 회사의 경우 ROWNUM을 명시하지 않거나 인라인 뷰 안쪽의 서브 쿼리에 INDEX SCAN이 불가능한 다양한 테이블들을 JOIN하고 WHERE 조건을 명시하여 부분범위 처리가 불가능하도록 잘못 구성된 SQL들이 다수 발견되어 위와 같은 형식으로 수정하여 처리하였다. 아래 예를 통해 잘못 구성된 SQL과 개선된 SQL을 확인해보자

1) 개선전
SELECT
 뉴스ID, 분류, 뉴스제목, 회사명, 뉴스게시일, READ_CNT, TOTAL
 , DECODE(B.분류이름, NULL, '기타', B.분류이름) AS 분류명
FROM (
 SELECT ROWNUM AS RN, tb.*
 FROM
 ( SELECT 뉴스ID, 뉴스제목, 뉴스게시자, 뉴스게시일, 회사명
 , SUBSTR(NVL(분류코드1, 분류코드2), 0, 3) AS 분류
 , NVL(READ_CNT,0) AS READ_CNT
 , COUNT(*) OVER() AS TOTAL
 FROM 뉴스
 WHERE 분류코드2  'XXXX'
 ORDER BY 뉴스 게시일 DESC, 뉴스 종류 DESC
 )tb
 ORDER BY rn
) A
, (SELECT 분류이름, 분류코드 FROM 분류테이블 WHERE 분류그룹 = '1111') B
WHERE A.분류= B.분류코드(+)
AND TRUNC((RN-1) / TO_NUMBER(:한페이지출력수)) = (TO_NUMBER(:페이지) -1)

 stage count cpu elapsed current query  disk  rows
------ ----- ---- ------ ------- ------ ----- -----
 parse     1 0.00   0.00      0      0     0     0
 exec      1 0.00   0.00      0      6     0     0
 fetch     1 2.61   7.89   1435 201019     0    15
---------------------------------------------------
  sum      3 2.61   7.89   1435 201025     0    15
 

 
2) 개선후

SELECT
 뉴스ID, 분류, 뉴스제목, 회사명, 뉴스게시일,READ_CNT, C.TOTAL
 , DECODE(B.분류이름, NULL, '기타', B.분류이름) AS 분류명
FROM (
 SELECT /*+ index_desc(뉴스 IX_뉴스) */ *
 FROM
 ( SELECT 뉴스ID, 뉴스제목, 뉴스게시자, 뉴스게시일, 회사명
 , SUBSTR(NVL(분류코드1, 분류코드2), 0, 3) AS 분류
 , NVL(READ_CNT,0) AS READ_CNT
 , ROWNUM RN
 FROM 뉴스
 WHERE 분류코드2  'XXXX'
 ORDER BY 뉴스게시일 DESC, 뉴스종류 DESC
 )
 WHERE RN <= (:페이지 * :한페이지 출력수)
 AND TRUNC((RN-1) / TO_NUMBER (:한페이지출력수)) = (TO_NUMBER(:페이지) -1)
) A
, (SELECT 분류이름, 분류코드 FROM 분류테이블 WHERE 분류그룹 = '1111') B
, (select COUNT(분류코드2) TOTAL FROM 뉴스 WHERE 분류코드2 <> 'XXXX') C
WHERE A.분류= B.분류코드(+) 

 stage count cpu  elapsed  current query disk rows
------ ----- ---- -------- ------- ----- ---- ----
 parse     1 0.00    0.00        0     0    0    0
 exec      1 0.00    0.00        0     0    0    0
 fetch     1 0.05    0.08       72  1851    0   15
--------------------------------------------------
 sum       3 0.05    0.08       72  1851    0   15

개선 사항 1 : WHERE ROWNUM 조건 누락 추가

개선 사항 2 : 인라인 뷰에서 부분범위 스캔이 가능하도록 인덱스 구성 > CREATE INDEX IX 뉴스 ON 뉴스(뉴스 게시일, 뉴스종류, 분류코드2);

개선 사항 3 : TOTAL은 위 생성한 인덱스를 활용하여 빠르게 스캔할 수 있도록 별도 구성