" p454-494 입니다"

제 2부 1.4. 부분범위 처리로의 유도
1.4.7. 저장형 함수를 이용한 부분 범위 처리
- 비절차형인 SQL 에서 절차형 처리를 가능하게 해준다

EX)
CREATE OR REPLACE FUNCTION GET_AVG_STOCK (
    v_start_date in date,
    v_end_date    in date,
    v_product_cd in varchar2 )
    RETURN number IS
    RET_VAL number(14);
BEGIN
    SELECT SUM(stock_qty) / (v_start_date - v_end_date) ) into RET_VAL
    FROM PROD_STOCK
    WHERE product_cd=v_product_cd
        AND stock_date between v_start-date and v_end_date;
    RETURN RET_VAL;
END GET_AVG_STOCK;

SELECT product_cd, product_name,
    GET_AVG_STOCK(product_cd, :b1, :b2) avg_stock
FROM PRODUCT
WHERE category_cd='20';

전체처리가 없음

가) 확인자 역할의 M 집합 처리를 위한 부분범위처리

M 집합을 단지 확인자 역할로 조인을 했다면 원래 집합으로 돌아가기 위해 GROUP BY,DISTINCT 등의 부담이 많은 쿼리를 취애야함
서브쿼리를 활용하는 방법도 불필요한 자료를 처리해야하는 경우가 생길수도 잇음

저장형 함수를 작성후 WHERE절에서 호출하여 조건 처리를 하고 SELECT LIST 문에서 값을 출력하는 형태
-> 저장형 함수를 중복수행하는 문제가 있을수 있음

인라인뷰를 활용하여 수행하는 방법
EX)
    CUST_ARR_FEE_FUNC(고객번호, 시작일,종료일) -> 요금총액 RETURN

    SELECT cust_no,cust_name, bill_tot, ..
    FROM (SELECT ROWNUM, cust_no,cust_name,
        CUST_ARR_FEE_FUNC(cust_no,:b1,:b2) bill_tot,...
            FROM customer
            WHERE cust_status='ARR' )
    WHERE bill>=1000000
        AND ROWNUM <= 30;

Execution Plan
----------------------------------------------------------
SELECT    STATEMENT
    COUNT (STOPKEY)
        VIEW    ............................. (b)
            COUNT    ....................... (a)
                TABLE ACCESS (BY ROWID) OF 'CUSTOMER'
                    INDEX (RANGE SCAN) OF 'CUST_STATUS_IDX'


서브쿼리의 rownum (A) 의 역할; 서브쿼리에서 ROWNUM의 확보요청으로 옵티마이져가 서브쿼리를
 VIEW로 (B) 생성한후 조건절에서 처리가능하게 됨 ( 저장형 함수를 한번만 수행)


나) 특정 부분만 부분범위처리로 유도

출력해야할 자료자체의 범위가 넓어 실행계획의 조정만으로 속도 향상을 기대하기 힘들때

EX)
    순수sql)
        SELECT sal_dept, agent_name, jan_tot,feb_tot ...,dec_tot     
        FROM
            (SELECT sal_dept,
                        SUM(decode(substr(sal_date,1,6),:year||'01',sal_amt)) jan_tot,
                        SUM(decode(substr(sal_date,1,6),:year||'02',sal_amt)) feb_tot,
                            ........................................................
                        SUM(decode(substr(sal_date,1,6),:year||'03',sal_amt)) dec_tot
            FROM sales
            WHERE sal_date like :year||'%'
            GROUP BY sal_dept
            ORDER BY jan_tot DESC ) s, agent a
    WHERE a.agent_no = s.sal_dept
            AND a.loc= :b1;

    sales 테입을 조건의 년도에 맞게 전체 가공한후 agent 테이블과 조인하여 최종결과 출력
    (sales 자료의 양에 따라 엄청난 access를 가져올수 있음)

   저장형함수 활용)

    GET_AGENT_SALAMT ( 판매처, 년월)  -> 판매총액 출력

  SELECT sal_dept, agent_name, jan_tot,
             GET_AGENT_SALAMT (sal_dept, :year||'02') feb_tot,
            ........................................................
             GET_AGENT_SALAMT (sal_dept, :year||'12') dec_tot
  FROM ( SELECT sal_dept, nvl (sum(sal_amt),0) JAN_TOT,
            max(agent_name) agent_name
            FROM sales s,agent a
            WHERE s.sal_dept=a.agent_no
                AND a.loc = :b1
                AND s.sal_date like :year || '01'
            GROUP BY sal_dept
            ORDER BY jna_tot desc);

첫달만 group by 로 계산하하고 메인쿼리의 SELECT 문에서 GET_AGENT_SALAMT를 호출하여 첫쿼리의 결과값을 활용하여
저장형 함수를 호출하여 각각 의 범위를 최대한 줄임

1.4.8. 쿼리의 분리를 이용한 부분범위처리

SQL)
    SELECT deptno, y.ename, y.empno, y.job, sal_tot, comm_tot
    FROM (SELECT empno,     
                        SUM(sal_amt) sal_tot,
                        SUM(comm)   comm_tot
             FROM salary s
             WHERE s.deptno like '12%'
                AND s.sal_date between '20050101' and '20051231'
            GROUP BY empno) x, employee y
    WHERE y.empno=x.empno;

    부서번호가 12로 시작하교 2005년에 지급한 급여의 총액(salary)을 기본적은 사원정보(employee) 와 함께 출력
    salary 테일블을 조건에 따라 전체 group by하고 다시 employee로 조인하여 결과출력

 
1.4.9. 웹 게시판에서의 부분범위처리
가) 웹 게시판 부분범위처리 사례 1 (NON-UNIQUE INDEX)

    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(y cust_name_idx) */
            ROWIDTOCHAR(rowid) rid, cust_name, ...
    FROM cust_table y
    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;

각 쿼리의 :v2 변수로 인해 호출조건이 특정지어지어 'FIRST','NEXT','PREV' 세쿼리중 하나만 수행되어짐
v3: 은 고객이름
v4: 는 rowid  값으로 'next'일경우 큰값중 25개 ,prev는 작은값으로 25개 씩 출력

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

기본키: '게시판ID+작성일자+글번호'

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 RNUM <= 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;

INDEX_DESC는 최근값부터 출력하기 위해사용
초기 호출시는 INIT_DT는 SYSDATE, V_NUM의 MAX값을 준후 호출
SW 변수로 버튼에 따른 쿼리 수행 조정

다) 웹 게시판 부분범위 처리 사례 3(처음-이전-다음-끝)
기본키: CID+CSEQ
결합인덱스: CDAY+CID+CSEQ

SELECT CDAY, CUST_NM, CID, CSEQ, CTEXT
FROM ( SELECT /*+ INDEX_ASC (a IDX 01) */
                    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 b:12
            AND rownum <=25
)

order by cday,cid,cseq



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

두단계로 나누어서 처리 하단부분에 각 페이지 링크를 위한 쿼리

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 = :v_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;

->게시판 목록 하단에 페이지 처리

  이전 1,2,3,4,5,6,7,8,9,10 다음

일경우 메인쿼리의 rnum으로 각 페이지의 시작페이지들을 출력
각 페이지들이 클릭될경우 다음 쿼리를 호출하여 페이지별 목록 출력

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 (계층구조의 처리)

REPLY 에 REPLAY에 달리는 형태의 게시판

절차형 처리를 가미하고 쿼리를 분리하여 처리

DECLARE
    CURSTOR c1 (start_id NUMBER) IS
        SELECT /*+ index_asc (a idx1) */
                rownum as rnum, id , pid
        FROM BILLBOARD a
        WHERE sw=1 and id>=start_id
            and rownum <=11;

    COURSOR c2(root_id NUMBER) IS
        SELECT rownum as rnum2, id, pid,
                lpad (' ',2*level-1) || comments as comm,
                sys_connect_by_path(id, '/') as path
        FROM BILLBOARD
        CONNECT BY pid=PRIOR id
                and rownum <=11
        START WITH id= root_id;

    TYPE IdTAbTyp is TABLE of billboard.id%TYPE
                    index by binart_integer;
    TYPE CommTabTyp is TABLE of billboard.comments%TYPE
                    index by binary+integer;

    ret_id          IdTabTyp;
    ret_root       IdTabTyp;
    ret_comm     CommTabTyp;
    ret_num       IdTabTyp;
    root_id         number;
    first_seq       number := &seq;
    start_id        number := &start_id;
    i                  binary_integer :=0;

BEGIN
    FOR c1_rec IN c1(start_id) LOOP    /* 본문글 액세스*/
        IF c1_rec_rnum > 11 OR i >10 THEN EXIT;
        END IF;
        root_id := c1_rec.id;
        FOR c2_rec IN c2(root_id) Loop   /* 계층구조 전개*/
            IF i> 10 THEN EXIT;
            END IF;
            IF (c1_rec.id = start_id and c2_rec.rnum2 >= first_seq) OR
                (c1_rec.id <> start_id ) THEN
                i := i+1;   /* 배열에 저장 */
                ret_id(i) :=c2_rec.id;
                ret_root(i) :=substr(c2_rec.path,2,instr(c2_rec.path, '/',2) -2);
                ret_comm(i) := c2_rec.comm;
                ret_num(i)    := c2_rec.rnum2;
            END IF;
        END LOOP;
    END LOOP;

    FOR i IN 1..11 LOOP        
        DBMS_OUTPUT.PUT_LINE(ret_id(i)||' '||ret_root(i)||' ' ||   /*화면으로 출력*/
                                            ret_comm(i)||' '||ret_num(i));
    END LOOP;
END;

출력된 결과를 사용하는 애플리케이션에서 처리하여 결과 출력

이전페이지 이동시

DECLARE
    CURSOR c1(start_id NUMBER) IS
        SELECT /*+ index_desc(a idx1) */
                   rownum as rnum, id, pid
        FROM BILLBOARD a
        WHERE sw=1 and id<=start_id
            and rownum <=11;

    CURSOR c2(root_id NUMBER) IS
        SELECT rownum as rnum2, id, pid,
                lpad(' ',2*level-1) || comments as comm,
                sys_connect_by_path(id,'/') as path
        FROM BILLBOARD
        CONNECT BY pid=PRIOR id
                and rownum <= 11
        START WITH id=root_id;

    TYPE IdTabTyp is TABLE of billboard.id%TYPE
            index by binary_integer;
    TYPE CommTabTyp is TABLE of billboard.comments%TYPE
            index by binary_integer;
    ret_id    IdTabTyp;
    ret_root     IdTabTyp;
    ret_comm    CommTabTyp;
    ret_num    IdTabTyp;
    root_id     number;
    last_rnum    number :=&seq;
    start_id    number :=&start_id;
    i            binary_integer := 0;
    j            binary_integer := 0;
    cnt        number :=0;

    BEGIN
        FOR c1_rec IN c1 (start_id) LOOP  /* 계층구조전계 */
            IF c1_rec.rnum > 11 OR cnt > 21 THEN EXIT;
            END IF;
            root_id :=c1_rec.id;

            FOR c2_rec IN c2(root_id) LOOP
                IF c2_rec.rnum2 <= last_rnum THEN
                    i := c2_rec.rnum2;
                    ret_id(i) := c2_rec.id;
                    ret_root(i) :=substr(c2_rec.path,2,instr(c2_rec.path,'/',2)-2);
                    ret_comm(i) :=c2_rec.comm;
                    ret_num(i) :=c2_rec.rnum2;
                END IF;
            END LOOP;

            last_rnum := 999;

            FOR j IN reverse 1..i LOOP    /* 배열에 저장 */
                cnt :=cnt+1;
                ret_id(112-cnt) :=ret_id(j);
                ret_root(112-cnt) :=ret_root(j);
                ret_comm(112-cnt) :=ret_comm(j);
                ret_num(112-cnt) := ret_num(j);
            END LOOP;
        END LOOP;

        FOR j IN 101..111 LOOP   /* 화면에 출력 */
            BEGIN
                DBMS_OUTPUT.PUT_LINE (ret_id(j)||' '||ret_root(j)||' '||
                                                    ret_comm(j)||' '||ret_num(j)) ;
                EXCEPTION
                    WHEN NO_DATA_FOUND THEN i:=1;
            END;
        END LOOP;
END;