1.4.9. 웹 게시판에서의 부분범위처리

웹 어플리케이션의 세션 유지는 페이지 단위이다.(앞서 페치한 것을 재사용할 수 없다.)
컴포넌트가 한번의 전체 범위의 데이터를 모두 액세스한후 페이지를 분할하여 관리하는 방식을 사용하지만 처리범위가 매우 넓거나 다수의 사용자가 동시에 사용하는 경우라면 심각한 문제를 일으킬 수 있다.
현재 웹 환경은 다수의 사용자가 넓은 범위의 데이터를 사용하므로 한 페이지에 추출할 만큼의 엑세스가 가장 바람직하다.

웹게시판의 부분범위처리를 위한 기본원리

인덱스를 활용한 정렬을 통해 순서대로 원하는 만큼만 액세스하고 그 다음은 앞서 패치했던 마지막 건의 정보를 이용해 그 후 원하는 만큼만 액세스하는 방식
다음 페이지를 액세스 하였을 때 새로운 세션으로 시작되었음에도 불구하고 마치 기존의 세션이 계속 유지된 것과 같은 효과
rownum을 이용하여 원하는 만큼만 잘라오는 방식
연쇄(concatenation)실행계획을 활용하는 방식
1. 처리주관조건이 OR나 IN일 때 각각의 대상만을 액세스하도록 분리하여 추출하여 불필요한 액세스가 발생하지 않도록 한다.
2. >= 연산의 경우에도 처리량이 크기 때문에 OR를 이용하여 몇개의 독립적인 액세스 단위로 실행계획을 나누어 각각 부분범위로 처리한다.


인덱스: SAL_DATE + SEQ

1) SELECT *
FROM SALES
WHERE SAL_DEPT = '1230'
AND SEQ = 111;

2) SELECT *
FROM SALES
WHERE SAL_DEPT >= '1230'
AND SEQ >= 111;

1)의 경우 시작점이 있지만 2)의 경우 SEQ의 경우 불필요한 액세스 양이 증가한다.
원하는 방식으로의 처리를 위해서는 SAL_DEPT||SEQ로 조건절을 변경후 함수기반인덱스를 생성하거나
아래와 같이 연쇄 실행계획을 이용한다.

=>


1) SELECT *
FROM SALES
WHERE (SAL_DEPT > '1230') ---------------------(2)
OR (SAL_DEPT = '1230' AND SEQ = 111); ---------------------(1)

연쇄 실행 계획은 WHERE 절에서 가장 나중에 기술한 조건부터 처리하여 완료되면 다음 실행단위로 넘어간다.
시작점을 찾고 스캔하는 모습을 띄고 있으며 각 단위가 부분범위 처리를 하여 효율적이다.

웹게시판 부분범위처리 사례1(NON-UNIQUE INDEX)

이전/다음 페이징(기본형)
고객명(cust_name) non unique index일때
union all 역시 연쇄 실행 계획의 일종
옵티마이저는 컬럼보다 변수/상수 비교(:V2)를 먼저 수행하므로 아래 세개의 SELECT 문중 단 하나만 성공
USE_CONCAT: OR 조건절에 대해서 연쇄 실행계획을 수립
어떤 페이지를 요구하더라도 항상 추출할 대상만 액세스하므로 효율적이다.


select /*+ index(w cust_name_idx */ 
       rowidtochar(rowid) rid, cust_name, ...
  from cust_table w
 where :v2 = 'FIRST' and cust_name like :v1 || '%'
   and rownum <= 25
union all
select /*+ use_concat index(x cust_name_idx */ 
       rowidtochar(rowid) rid, cust_name, ...
  from cust_table x
 where :v2 = 'NEXT' 
   and (cust_name > :v3 or
       (cust_name = :v3 and rowid > chartorowid(:v4) ) )
   and cust_name like :v1 || '%'
   and rownum <= 25
union all
select /*+ use_concat index_desc(x cust_name_idx */ 
       rowidtochar(rowid) rid, cust_name, ...
  from cust_table x
 where :v2 = 'PREV' 
   and (cust_name < :v3 or
       (cust_name = :v3 and rowid < chartorowid(:v4) ) )
   and cust_name like :v1 || '%'
   and rownum <= 25
 order by cust_name, rid

웹게시판 부분범위처리 사례2(UNIQUE INDEX)

이전/다음 페이징
기본키(billboard_uk)는 '게시판id+작성일자+글번호'로 구성
작성된 글의 생성 역순으로 추출
:INIT_DT, :V_NUM은 처음/다음일때 각각 다른값을 주어야 한다는 것 주의(처음: SYSDATE/최대값)
(26-rownum) rnum: 이전 페이지의 경우 인덱스 ASC 순서로 뽑았기 때문에 이와 같이 보수로 표현하여 최신순으로 정렬될 수 있도록 처리


select bbs_id, 작성일자, 글번호, rnum
  from (select /*+ use_concat  index_desc(a billboard_uk) */
               rownum rnum, bbs_id, 작성일자, 글번호, 글내용
            from billboard a
         where :sw = 'NEXT'
           and bbs_id = :bid
           and (작성일자 < :init_dt or 
               (작성일자 = :init_dt and 글번호 < :v_num) )
           and rownum <= 25
         union all
        select /*+ use_concat  index_asc(a billboard_uk) */
               (26-rownum) rnum, bbs_id, 작성일자, 글번호, 글내용
           from billboard a
        where :sw = 'PREV'
          and bbs_id = :bid
          and (작성일자 > :init_dt or 
              (작성일자 = :init_dt and 글번호 > :v_num) )
          and rownum <= 25
       )
 order by rnum;

웹게시판 부분범위처리 사례3(처음,이전,다음,끝)

인덱스: cday + cid + cseq (정렬 처리를 위한 인덱스)


SELECT CDAY, CUST_NM, CID, CSEQ, CTEXT
  FROM (SELECT /*+ INDEX_ASC(a IDX01) */
               CDAY, CUST_NM, CID, CSEQ, CTEXT
          FROM CSTAB a
         WHERE :SW = 'FIRST'
           AND CDAY BETWEEN :B11 AND :B12
           AND ROWNUM <=25
         UNION ALL
        SELECT /*+ INDEX_DESC(a IDX01) */
               CDAY, CUST_NM, CID, CSEQ, CTEXT
          FROM CSTAB a
         WHERE :SW = 'LAST'
           AND CDAY BETWEEN :B11 AND :B12
           AND ROWNUM <=25
         UNION ALL
        SELECT /*+ USE_CONCAT INDEX_ASC(a IDX01) */
               CDAY, CUST_NM, CID, CSEQ, CTEXT
          FROM CSTAB a
         WHERE :SW = 'NEXT'
           AND ((CDAY > :B100) OR (CDAY = :B100 AND CID > :B20
            OR (CDAY = :B100 AND CID = :B20 AND CSEQ > :B27) )
           AND CDAY BETWEEN :B11 AND :B12
           AND ROWNUM <=25
         UNION ALL
        SELECT /*+ USE_CONCAT INDEX_DESC(a IDX01) */
               CDAY, CUST_NM, CID, CSEQ, CTEXT
          FROM CSTAB a
         WHERE :SW = 'PREV'
           AND ( (CDAY < :B100) OR (CDAY = :B100 AND CID < :B20
            OR (CDAY = :B100 AND CID = :B20 AND CSEQ < :B27) )
           AND CDAY BETWEEN :B11 AND :B12
           AND ROWNUM <=25 )
   ORDER BY CDAY, CID, CSEQ

웹게시판 부분범위처리 사례4(SET단위처리)

넘버 페이징
세트간의 이동은 사례 이전/다음 페이징과 동일(세트가 시작될 시작점만 알면 된다.)
인덱스: bbs_id + cre_dt + num
rownum <= 201:한 세트의 총 글수는 200개이나 다음 세트의 시작점을 확보하기 위해 1개의 로우를 추가
예: 2세트에서 1세트로 이동할때


SELECT RNUM, BBS_ID, CRE_DT, NUM
  FROM (SELECT /*+ USE_CONCAT INDEX_DESC(a bbs_idx1) */
               ROWNUM RNUM, BBS_ID, CRE_DT, NUM
          FROM BILLBOARD a
         WHERE :SW = 'NEXT' AND BBS_ID = :V_BBS
           AND ( CRE_DT < :V_INIT_DT
            OR ( CRE_DT = :INIT_DT AND NUM <= :V_NUM) )
           AND ROWNUM <= 201
         UNION ALL
       특정 페ㅣ지를 액세스하는 SELECT /*+ USE_CONCAT INDEX_ASC(a bbs_idx1) */
               ((20*10)+2 - ROWNUM) RNUM, BBS_ID, CRE_DT, NUM
          FROM BILLBOARD a
         WHERE :SW = 'PREV' AND BBS_ID = :V_BBS
           AND ( CRE_DT > :V_INIT_DT
            OR ( CRE_DT = :INIT_DT AND NUM >= :V_NUM) )
           AND ROWNUM <= 201 
       )
 WHERE RNUM IN (1,21,41,61,81,101,121,141,161,181,201)
 ORDER BY RNUM

특정 페이지를 액세스하는 SQL
다음 세트를 처리한 SELECT 문과 유사
각 변수 값은 앞서 추출한 각 페이지의 첫번째 로우의 값
다른 테이블과 조인하여 정보를 추가할 수는 있지만 정렬 순서에 문제가 발생하지 않도록 주의해야 한다.


SELECT /*+ USE_CONCAT INDEX_DESC(a bbs_idx1) */ bbs_id, cre_dt, num, c_text
FROM BILLBOARD a
WHERE bbs_id = :bbs
AND ( cre_dt < :v_init_dt
OR ( cre_dt = :v_init_dt AND num <= :v_num ) )
AND ROWNUM <= 20;

웹게시판 부분범위처리 사례5(계층구조의 처리)

답글에 다시 답글이 달릴 수 있는 계층 구조의 게시판 처리
하위 계층의 중간에서 페이지가 끝났을때 기존과 같이 현재 페이지의 마지막이나 처음 로우 정보만으로는 부족하다.
페이지가 어느 단계에서 잘렸든 최상위의 원본글 정보를 가지고 있어야 한다.
계층 구조의 전개를 부분범위로 처리하는 방법
1. 처음 페이지를 처리하는 방법
원본글 들만 이용해 원하는 순서로 계층구조를 전개해 가다가 페이지가 채워지면 처리를 멈춘다.
이전/다음 처리를 위해서 최상위 레벨의 식별자 정보를 보유한다.
2. 다음 페이지를 처리하는 방법
마지막 글이 원본글로 부터 몇번째 순번의 글인지 알아야 한다.
해당 순번의 다음번 부터 잘라온다.
=> 추출 대상 로우는 한테이지에 나타나는 데이터의 수보다 많아진다.
3. 이전페이지를 처리하는 방법
=> SQL을 분리하여 절차형 처리를 가미해야한다.


SELECT ID, PID, C_TEXT,SUBSTR(PATH,3,3) * 1 PARENT_ID -------(e)
FROM (SELECT ROWNUM RNUM, ID, PID, WRITER,
             LPAD(' ', 2*LEVEL-1)||C_TEXT C_TEXT,
             SYS_CONNECT_BY_PATH(TO_CHAR(ID,'999'),'/') PATH -------(d)
        FROM BILLBOARD
     CONNECT BY PID = PRIOR IS
            AND ROWNUM <=10 + :CNT -------(b)
       START WITH PARENT_SW = 1 -------(a)
              AND ID >= :START_ID
     )
 WHERE RNUM BETWEEN :CNT AND 10+ :CNT -------(c)
 AND rownum <= 10;

pid + id, paraent_sw + id 의 두개의 인덱스 필요
처음 페이지 처리와 다음 페이지 처리를 할 수 있는 쿼리
:cnt:원본글에서 몇번째의 순번인지
:start_id: 페이지 시작 전의 원본글 번호(d, e를 통해 미리 찾아두어야 하는 부분)
SQL 처리 경로:(a) 행하여 원본글로부터 계층구조를 시작 -> (b) 를 통해 계층 구조를 전개(rownum 조건을 여기에 둔 이유는 connect by 절에 조건을 기술하면 계층구조를 진행해가면서 체크가 일어나기 때문, where 조건에 기술하면 계층구조에 전개 이후에 조건을 체크) -> ©에서는 rnum을 이용하여 원하는 만큼 잘라냄
문제: 결국엔 start with에서 준 전체 원본글에 대해 모두 전개가 수행되어 부분 범위 처리를 할 수 없다.(계층 구조의 처리는 각 레벨을 전개할 때 액세스하는 즉시 추출할 수가 없기 때문, 옵티마이저의 처리방법)
아래와 같이 쿼리를 분리하여 부분범위 처리를 해야 한다.

p490

원본글을 액세스 하는 부분과 답글을 전개하는 부분으로 쿼리를 분기(각 원본글에 대해 순환전개를 수행)
10개의 글을 게시하며 다음 페이지 처리를 위해 11개의 글을 추출

p492

근본적으로는 다음 페이지를 처리하는 것과 유사
(a)에서 원본글을 액세스할때 인덱스를 역순으로 액세스한다.
배열에 저장하는 부분이 많은 차이점이 있다.(나중 글을 먼저 옮기면서 전개 순서를 원래대로 해야 하기 때문에 loop를 역으로 수행시키고 (112-CNT)를 함

문서에 대하여

이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 이화식님의 새로쓴 대용량 데이터베이스 솔루션을 참고했습니다.*
이 문서를 다른 블로그나 홈페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^\^

1.4.9. 웹 게시판에서의 부분범위 처리 (by tlswofl) [2011.08.16]