SQL BOOSTER 페이징 기술

 

1. 페이징의 종류 

 

페이징은 3가지 종류가 있다. 

1. WAS 페이징: 모든 데이터를 가져와 WAS에서 페이징 처리 하는 방법
2. DB 페이징: 데이터베이스에서 페이징에 필요한 만큼의 데이터만 조회 (성능 고려x)
3. DB-INDEX 페이징: 인덱스를 이용해 페이징에 필요한 데이터만 정확히 읽는 방법 (성능 고려 O)

SQL BOOSTER

이 중 WAS페이징은 사용하면 안 되는 방법이다. DB페이징과 DB-INDEX 페이징은 비슷한 기술이다. 

SQL 성능을 최적화 했는지에 따라 두 기술을 분류할 수 있다. 

 

DB-INDEX 페이징은 상황에 따라서 페이징이 불가능할 때도 있다.  (부분 범위 처리 페이징, NO-SORT 페이징)

 

1.2 DB 페이징

 

ROWNUM을 이용해 페이징에 필요한 데이터만 골라내는 흔히 사용하는 기법이다. 

SELECT T1.ORD_SEQ, T1.ORD_YMD, T1.CUS_ID , T2.CUS_NM, T3.RGN_NM, T1.ORD_ST, T1.ITM_ID
FROM T_ORD_JOIN T1, M_CUS T2, M_RGN T3
WHERE T1.ORD_YMD LIKE '201703%'
AND T1.CUS_ID = T2.CUS_ID
AND T3.RGN_ID = T2.RGN_ID
ORDER BY T1.ORD_YMD DESC, T1.ORD_SEQ DESC;

 

1.2.1 첫 페이지 조회 

 

SELECT *
FROM (
SELECT T1.ORD_SEQ, T1.ORD_YMD, T1.CUS_ID , T2.CUS_NM, T3.RGN_NM, T1.ORD_ST, T1.ITM_ID
FROM T_ORD_JOIN T1, M_CUS T2, M_RGN T3
WHERE T1.ORD_YMD LIKE '201703%'
AND T1.CUS_ID = T2.CUS_ID
AND T3.RGN_ID = T2.RGN_ID
ORDER BY T1.ORD_YMD DESC, T1.ORD_SEQ DESC
) T_PG1
WHERE ROWNUM <=30;

 

• SQL을 인라인 뷰로 처리하고, 인라인-뷰 바깥에서 ROWNUM을 사용한다. 
• ORDER BY와 ROWNUM의 위치가 매우 중요하다. 
• ORDER BY는 인라인-뷰 안쪽, ROWNUM 인라인뷰 바깥에 위치해야함

SQL BOOSTER

페이징이 정확하게 처리되면, COUNT STOPKEY보다 SORT ORDER BY STOPKEY가 먼저 시행되어야 한다. 

즉, 정렬 후에 데이터를 뽑아야한다. 

 

1.2.2 페이지 별 조회 

 

SELECT *
FROM (
     
      SELECT ROWNUM RNO, T1.*
      FROM (
      
      SELECT T1.ORD_SEQ, T1.ORD_YMD, T1.CUS_ID , T2.CUS_NM, T3.RGN_NM, T1.ORD_ST, T1.ITM_ID
	  FROM T_ORD_JOIN T1, M_CUS T2, M_RGN T3
	  WHERE T1.ORD_YMD LIKE '201703%'
      AND T1.CUS_ID = T2.CUS_ID
      AND T3.RGN_ID = T2.RGN_ID
      ORDER BY T1.ORD_YMD DESC, T1.ORD_SEQ DESC
      ) T1
      WHERE ROWNUM <= 60
      
      )T2
)
WHERE T2.RNO >= 31;

 

두 번째 페이지의 마지막까지 조회 되도록 ROWNUM <= 60 조건을 사용하고 있다. 

인라인 뷰에서 해당 조건을 사용했음으로 1~60까지 나오고, 다시 마지막 뷰에서 RNO >= 31조건을 사용했음으로

31~60까지 조회된다.

 

이처럼 인라인-뷰를 여러번 사용해야 페이지를 제대로 조회할 수 있다. 이를 줄이기 위해 ROW_NUMBER를 사용할 수도 있지만, ROWNUM이 성능이 더 좋다. 

 

2. DB-INDEX 페이징 

 

인덱스를 추가해 DB페이징의 성능을 끌어올리는 방법이다.  DB-INDEX페이징이란 인덱스를 이용해 필요한 데이터만 정확히 읽어내는 방법이다. 

 

DB인덱스 페이징을 구현하려면, SQL의 조건절에만 인덱스를 만드는 것이 아니라 페이징에 사용되는 ORDER BY 컬럼까지 고려해 인덱스를 생성해야 한다. 

 

인덱스 컬럼을 정하는 기준은 다음과 같다. 

1. WHERE 절에 조건으로 사용된 컬럼을 복합 인덱스의 선두 컬럼으로 사용한다.
   1. 조건이 여러 개라면, 같다 조건의 컬럼을 앞쪽에 범위 조건을 뒤쪽에 놓는다.
2. ORDER BY에 사요된 컬럼을 1번에서 정의한 컬럼 뒤에 차례대로 위치시킨다.

*단 규칙에 따라 인덱스를 만드는 것 보다, 실행계획을 확인하는 것이 가장 중요하다.

 

CREATE INDEX X_T_ORD_JOIN_PG1 ON T_ORD_JOIN(ORD_YMD, ORD_SEQ);

SELECT *
FROM (
     
      SELECT ROWNUM RNO, T1.*
      FROM (
      
      SELECT T1.ORD_SEQ, T1.ORD_YMD, T1.CUS_ID , T2.CUS_NM, T3.RGN_NM, T1.ORD_ST, T1.ITM_ID
	  FROM T_ORD_JOIN T1, M_CUS T2, M_RGN T3
	  WHERE T1.ORD_YMD LIKE '201703%'
      AND T1.CUS_ID = T2.CUS_ID
      AND T3.RGN_ID = T2.RGN_ID
      ORDER BY T1.ORD_YMD DESC, T1.ORD_SEQ DESC
      ) T1
      WHERE ROWNUM <= 60
      
      )T2
)
WHERE T2.RNO >= 31;

• 조인 조건은 UNIQUE INDEX가 걸려있다. 나머지 조건
• ORDER BY 조건인 ORD_YMD,ORD_SEQ가 차례대로 사용되었다. 

실행계획을 확인해 보면 0.01초로 성능이 개선되었다. 전체 Bufferes는 2,681에서 134 개선되었다. 

 

SQL BOOSTER

 

인덱스를 만드는 것만으로 DB 페이징이 DB-INDEX페이징으로 변경되고, 성능이 향상되었다. 

새로 만든 인덱스를 INDEX RANGE SCAN DESCENDING 방식으로 접근해 필요한 60건만 가져오고 있다. 

이처럼 60건만 가져올 수 있는 이유는, 조회하려는 데이터 순서와 인덱스의 리프 블록의 데이터 순서와 인덱스의 리프 블록

 

실행계획에서 DB-INDEX 페이징이 동작했는지 확인하는 항목들은 다음과 같다.

1. INDEX RANGE SCAN DESCNDING 혹은 ASCENDING 오퍼레이션이 있다.(상황에 따라 INDEX FULL SCAN)
2. 1번 항목에서 페이징한 건수만큼 혹은 약간 초과한 A-ROWS가 나와야한다. -> ORDER BY나 조건절, 인덱스 구성에 따라 A-Rows가 페이징 건수보다 높을 수 있다. (최대한 비슷하게 가져가야 큰 비효율이 없다)
3. 1번 항목 이후에 COUNT STOPKEY가 있어야한다. 

 

2.1 DB 인덱스 페이징 성능 한계

 

100번째 페이지가 조회되도록 SQL을 실행해보자 

--생략
   WHERE ROWNUM <= 100*30 --페이지 번호 * 페이지당 로우수 
   )T2
WHERE T2.RNO >= (100*30) - (30-1) -- (페이지번호 * 페이지당 로우수) - (페이지당 로우수-1)