서술형문제5. 페이징처리 SQL 작성법

고객상태코드 = 'AC'인 고객을 조회해서 등록일시,고객번호 순으로 출력하고자 한다. 출력하고자 하는 항목은 아래 그리드(Grid)와 같고, 가장 우측 최근접속일시는 최근 한 달 이내 마지막 접속일시를 의미하며, 접속이력이 없으면 Null을 출력한다.

※한달 전 날짜를 구하는 함수 -> trunc(add_months(sysdate,-1))

 

'조회/다음' 버튼을 누르면, 매번 20건씩 데이터를 읽어 그리드 화명에 추가(Append)하는 방식으로 화면 페이징 처리를 구현해야 하고, '파일로 출력' 버튼을 누르면 전체 조회 데이터를 파일로 일괄 저장하도록 구현해야 한다.

두 조회 버튼에 대한 1.최적의 SQL을 각각 작성하고, 2.최적의 구성안을 제시하시오

 

요건

1. 각 조회버튼에 대한 성능요건

   1) 조회/다음 : 응답속도(Response Time)을 빠르게 튜닝하는 것이 가장 중요

   2) 파일로 출력 : 전체 처리속도와 시스템 리소스 사용량을 최소화하는 것이 가장 중요

 

2. (조회/다음 버튼 클릭 시) 화면 페이징 처리 요건

   1) 조회/다음 버튼을 계속 눌러 뒤쪽 페이지로 많이 이동하는 경우가 간혹 있지만, 대개 3페이지 이내만 조회하고 멈추는 업무임( 페이지마다 인덱스 스캔 시작점을 찾기 위해 UNION ALL 방식으로 복잡하게 구현하지 않아도 된다는 의미임)

   2) 페이징 방식으로 조회하는 동안 새로운 데이터가 등록되거나 기존 데이터가 삭제되는 경우를 고려하지 않아도 됨

   3) 향후에 혹시 인덱스 구성이 변경되더라도 결과집합은 정확히 보장되도록 구현해야 함

 

3. View Merging, Join Perdicate Pushdown 등 Query Transformation이 작동하지 않는 DBMS 버전을 사용 중임

 

4.인덱스 설계 시, 성능에 도움이 안되는 컬럼을 추가하면 오히려 감정이 될 수 있으므로 주의

 

5. 병렬처리 불가

 

데이터 분포 및 테이블 구성

 

고객테이블

-비파티션

- 총 고객수 = 10만명

- 고객상태코드 'AC'인 고객수 = 2만명

 

고객접속이력 테이블

- 총 데이터 건수 : 1,000만건

- 접속일시 기준 월단위 Range 파티션

- 고객접속이력_PK은 Local Partitioned Index

 

 

정답 및 튜닝 Point

 

화면 페이징 처리용 SQL을 작성할 줄 알아야 합니다

해당 답안으로 제시된 SQL의 유일한 단점이 앞페이지에서 읽은 데이터를 다시 읽어야 하므로 뒤 페이지로 이동할수록 블록 I/O가 증가한다는 것입니다. 하지만 문제에서 3페이지 이내로 조회하는것이 대부분이라는 코멘트가 있으므로 상관없게 되었습니다. 실무에서도 가장 많이 쓰는 방법이라고 합니다.

 

참고로 앞 페이지에서 읽은 데이터를 다시 읽지 않게 구현하려면 UNION ALL 방식으로 매우 복잡하게 처리해야 합니다.

 

최근접속일시는 맨 바깥쪽 SELECT-LIST에서 스칼라 서브쿼리로 구현해야합니다. 화면에 출력하는 20건에 대해서만 스칼라 서브쿼리를 수행하도록 해야 하기 때문입니다.

 

인덱스 설계시에 파일 출력용(전체범위처리SQL)은 인덱스를 사용하면 오히려 비효율적입니다. 따라서 인덱스는 화면 페이징 처리용 SQL에 대해 최적화되도록 작성해야 합니다.

 

고객상태코드로 Range Scan하려면 선두컬럼은 고객상태고드여야 합니다. 부분범위처리가 가능하려면 order by절 컬럼인 등록일시, 고객번호를 뒤쪽에 추가하면 됩니다.

고객접속이력 인덱스의 경우에는 고객번호를 선두에두고 부등호조건인 접속일지를 뒤쪽에 추가하면 됩니다.

 

정답

페이징 처리 SQL

select b.고객번호,b.고객명,b.등록일시,b.연락처,b.주소,(select max(접속일시)

from 고객접속이력

where 고객번호 = b.고객번호

and 접속일시 >= trunc(add_months(sysdate,-1))

) as 최근접속일시
from
(
select rownum as rn,a.*
from
(
select 고객번호,고객명,등록일시,연락처,주소
from 고객

where 고객상태코드='AC'

order by 등록일시,고객번호
) a
where rownum <= :page * 20) b
where b.rn >= (:page -1) * 20 + 1

 

 

전체범위 처리 SQL

 

select a.고객번호,a.고객명,a.등록일시,a.연락처,a.주소,b.최근접속일시

from 고객 a, (select 고객번호,max(접속일시) from 고객접속이력 where  

접속일시 >= trunc(add_months(sysdate,-1) group by 고객번호) b

where a.고객번호 = b.고객번호

and a.고객상태코드='AC'

order by a.등록일시,a.고객번호

 

 

인덱스

 

고객 : 

CREATE INDEX 고객_IDX1 ON 고객('고객상태코드','등록일시','고객번호');

 

고객접속이력 :

CREATE INDEX 고객접속이력_IDX1 ON 고객접속이력('고객번호','접속일시') LOCAL;