ORACLE PAGING OPTIMIZER by VINS
/*
DECLARE
V_SQL : 비지니스 쿼리 부분
V_STRSEARCH : 쿼리에서 사용되어 질 기본 조건1 (커스트 마이징 가능)
V_ISACTIVE : 쿼리에서 사용되어 질 기본 조건2 (커스트 마이징 가능)
V_QUERY : 페이징 및 TOTAL COUNT 만드는 부분
V_PAGE_NO : 페이징 처리 하기 위한 페이지 숫자
V_PAGE_SIZE : 한 페이지에 보여줄 내용
BEGIN
--구현 쿼리
--페이징 쿼리
--디버깅 출력
END;
다른 비지니스 로직을 가진 쿼리를 해야 할 경우
V_SQL 쿼리 수정 및 V_SQL에 종속적인 V_STRSEARCH, V_ISACTIVE 부분은
맞게 수정 해야 하는 부분이다.
V_QUERY, V_PAGE_NO, V_PAGE_SIZE는 페이지을 위한 부분이므로 수정 할 필요 없음
그리고 1,2,3,4,5... 등 숫치 페이징을 해야 할 경우의 내용도 한번에 해결 할 수 있도록
정리해 두었다.
*/
DECLARE
V_SQL VARCHAR2(1000);
V_STRSEARCH VARCHAR2(1000) := 'SEARCH';
V_ISACTIVE CHAR(1) := 'Y';
V_QUERY VARCHAR2(1000);
V_PAGE_NO NUMBER := 1;
V_PAGE_SIZE NUMBER := 30;
BEGIN
--이 부분은 페이징을 적용해야 할 데이터에 따라 달라지는 부분이라 참조하는 쿼리가 필요 하다면 크게 신경 쓰지 말자
V_SQL :=' SELECT CACD,CTSCD,SUBJECT,SUMMARYTEXT,STARTDATE,LASTDATE,
DDATE, NOTETYPE,ISPAGE,LINKTYPE,LINKURL,IMGPATH,HIT,
REGDATE,NUM,ROWNUM RNUM
FROM ( SELECT /*+ INDEX_JOIN(CP_ANNOUNCE) */ CACD, CTSCD, SUBJECT,
SUMMARYTEXT, STARTDATE, LASTDATE, DDATE, NOTETYPE,
ISPAGE, LINKTYPE, LINKURL, IMGPATH, HIT, REGDATE,
ROW_NUMBER() OVER ( ORDER BY SORTING DESC,CACD DESC) AS NUM
FROM JAPANDEV.CP_ANNOUNCE
WHERE ISACTIVE = ''' V_ISACTIVE '''
AND STARTDATE > SYSDATE';
IF LENGTH(V_STRSEARCH) > 0 THEN
V_SQL := V_SQL ' AND SUBJECT LIKE ''' V_STRSEARCH '%''';
END IF;
V_SQL := V_SQL ' ) ORDER BY RNUM DESC';
--이 부분 부터 페이징 핵심--
V_QUERY := 'SELECT * FROM (
SELECT ROWNUM AS RN, A.*, COUNT(*) OVER() AS TOTAL_RECORD
FROM ( ' V_SQL ')
A )
WHERE RN BETWEEN (('V_PAGE_NO' - 1) * 'V_PAGE_SIZE') + 1
AND ('V_PAGE_NO * V_PAGE_SIZE')';
DBMS_OUTPUT.PUT_LINE(V_QUERY);
END;
위 내용을 토드의 Query Optimizer 를 이용해서 미리 테스트 해 보면
아래 (이미지)
위에서 공통적으로 나오는 용어가 COST, Cardinality, Bytes 등이 있다
이 수치들은 전부 낮은 숫자가 나오는 것이 좋지만 자세한 설명은 많이 길어지게 되므로
오라클 인덱스 및 튜닝에 대해서 자료를 찾아 보는 것이 좋을 것이다.
이외 Query Plan에서 Execute를 시키면 추가적으로 정보가 나오게 되는데
결과1)
Total CPU : 0.00
Physical Reads (물리적 읽기 수) : 0
Logical Reads (논리적 읽기 수) : 14
Scan Rows : 5
Scan Gets : 3
Memory : 1597
샘플 쿼리는 실행 결과가 결과1) 과 같은 내용의 출력 되었다. (첨부 이미지도 위 쿼리의 결과임)
왜 저렇게 빠른 쿼리가 나오는 걸까 하고 궁금하신분은 궁금해 하실 필요가 없다.
테이블에 데이터가 없기 때문에 저렇게 빠른 것이니까.
실제 수백만 로우의 데이터를 다룬다면 인덱스를 잘 사용해야 할 것이고
특히 desc, asc 등 범위나 순서가 관련 있는 컬럼들은 특별 관리 대상이다.
(인덱스를 asc로 잡아놓고 desc 소팅을 하면 결과가 뒤집히는 현상 때문에 인덱스가 없느니 못함)
매일 DB스케줄러에 재 인덱싱 작업도 실행되고 있어야 한다.(사람 없는 시간에...)
위 예제 쿼리는 수백만행이 있으면 다음 또 그 다음 페이지로 지나면서 조금씩 느려질 것이고
제일 마지막 페이지로 가게 되면 가장 느려지게 되므로 부하를 반토막 내고 싶다면 데이터의 Row를 상대로
뒤집어 가져오는 쿼리를 하나 더 만들어 놓고 IF...THEN...ELSE 를 사용해서 분기하는 것도 좋은 방법이다.
하지만
수백만행을 전부 읽는 사람 보다는 무심코 첫 페이지만을 클릭하는 사람이 95% 이상이라고 봤을때 쿼리만으로도
최적화 된 것이라고 하겠지만 메인 페이지에 공지사항 5개 , 이벤트 5개, 어떤 정보 5개 등등
이런 로직일 경우 XML을 자동(또는 관리자가 수동)으로 배포하게 한 후 XML을 AJAX로 읽어 들이는 방식을
추천한다.
늘 하는 말이지만 디비는 생각보다 약하고 가장 느린 선택이다.(그렇다고 FILE을 선택하라는 뜻은 아님 ㅋㅋ)
그럼 이만~
