Plsql 페이징 쿼리 처리 시 질문드립니다. 0 6 3,718

by 엉재 [PL/SQL] plsql with as 페이징 쿼리 [2013.02.22 15:02:11]



안녕하세요. 페이징 쿼리에 대해서 질문을 드리고자 합니다.

목록을 가져오는 프로시저를 생성했습니다.

기존에는 전체count를 가져오는 쿼리와 목록을 조회하는 쿼리로 두 개가 있었는데요.

제가 아래와 같이 하나의 쿼리로 전체건수와 목록을 동시에 가져오는 쿼리로 변경해 봤습니다.

프로시저 내용은 아래와 같습니다.


create or replace procedure test
(p_code     in varchar2,
 p_start_index in int,
 p_end_index   in int,
 p_apply_start_dt in varchar2,
 p_apply_end_dt   in varchar2,
 v_sale   out sys_refcursor,
 P_TOTAL_CNT out int,
 p_return    out int,
 p_err_msg   out varchar2
) as
v_oramsg     system_error_log.contents%type;    --FOR USER ORACLE MSG
v_errtitle   system_error_log.contents%type;    --FOR USER PROGRAMMER MSG
v_errshow    system_error_log.contents%type;    --FOR USER PROGRAMMER MSG

e_err    exception;   -- user Defined Exception for Pre-Process
e_usrerr     exception;   -- user Defined Exception for Pre-Process
e_usrerr_pre     exception;   -- user Defined Exception for Pre-Process
e_usrerr_main    exception;   -- user Defined Exception for Main-Process
e_usrerr_post    exception;   -- user Defined Exception for Post-Process

v_return     int   ;    -- 처리결과
v_err_msg    system_error_log.contents%type;  -- 오류메시지
v_prccount   number(10, 0);    -- 처리건수

v_test_cnt   number := 0; -- 전체건수


begin
    DBMS_APPLICATION_INFO.SET_MODULE(' TEST START', 'START'  );

    v_errtitle     := '<COMMON BODY>';
    v_return   := -1;


  v_errtitle := '[1/3] TEST_EXCEPTION - SELECT PAGIING LIST';
Open V_SALE for
SELECT *
   FROM (
    SELECT sale_date,
   memb_numb,
   code,
   memb_name,
   use_yn,
        ROW_NUMBER() OVER (ORDER BY apply_start_dt desc ) RNUM,
   count(*) over() totalcnt
FROM SALE
    WHERE CODE = P_CODE
  AND sale_date >= p_apply_start_dt
  AND sale_date <= p_apply_end_dt
   ) SALE
  WHERE RNUM BETWEEN v_start_index AND v_end_index
;


    v_return := 0;

    EXCEPTION
    WHEN OTHERS THEN
  v_errshow := SUBSTR(SQLERRM, 1, 150);
  v_return   := -1 ;
  v_test_cnt    := 0;
  RAISE e_usrerr;

    END ;

    P_TOTAL_CNT := v_test_cnt; --전체건수
    p_return    := v_return   ; --수행결과
    p_err_msg   := v_errtitle    ; --에러메시지

    DBMS_APPLICATION_INFO.SET_MODULE(' TEST COMPLETE', 'END');

    COMMIT;

EXCEPTION
    When e_usrerr Then
    ROLLBACK;
    p_return  := '-1';
    p_err_msg := v_errtitle || ' : ' || v_errshow;

    WHEN others THEN
    ROLLBACK;
    p_err_msg  := v_errtitle || ' : ' || substr(sqlerrm, 1, 150);
    p_return  := -1;
END
/

여기서 문제가 하나 있는데 쿼리 결과를 ref cursor로 v_sale이란 것으로 받는데

제가 원하는 것은 쿼리결과는 v_sale이라는 ref cursor로 반환하면 되는데

v_sale에 있는 totalcnt를 한번 더 불러서 p_total_cnt라는 변수로 할당하고 싶은데요.

with절이라던가 다른 고민을 해봤는데 쉽게 떠오르지가 않네요.

여러 고수님들의 답변 부탁드립니다.

 

by 마농 [2013.02.22 16:04:56]

최초 전체 데이터를 읽는 것은 동일합니다.
그러나...
ROW_NUMBER 만 사용할 경우 부분범위 처리가 가능하여
정렬을 위한 템프공간이 1페이지 만큼만 필요합니다.
그런데...
COUNT(*) OVER() 를 동시에 사용하게 되면 부분범위 처리가 되지 않아
정렬을 위한 템프공간이 전체 건수 만큼 필요합니다.
그만큼 정렬에 대한 부하가 커진다는 거지요.


쿼리를 분리할지... 하나로 합칠지는...
성능 비교를 꼼꼼하게 해보시고 하시는게 좋을 듯 합니다.


위 질문에 대한 답은 Fetch 를 하면 되지 않을가요?
해보지 않아서 모르겟네요.


by 마농 [2013.02.22 16:08:18]

정렬공간이 1 페이지 만큼 필요한건 아닌것 같네요.
v_end_index 만큼의 공간이 필요하겠네요.


by 엉재 [2013.02.22 16:18:40]

마농님 답변 감사합니다..

인라인뷰에 있는 row_number만 사용한다 하더라도 sale_date에 대한 between조건이 있기 때문에

부분범위 처리가 가능하지 않을까 생각돼서 count(*) over()를 넣은 건데요.

현재는 같은 조건에서 한 개는 count하는 것이고 다른 한개는 목록을 가져오니

한 개로 합칠 수 있으면 좋다는 생각이 들었습니다.

그리고 fetch를 하게 되면 기존 v_sale ref cursor의 값이 초기화 된다는 얘기를 들어서

다른 방법이 있는 지 문의드렸습니다. with절을 사용하는 것은 어려운 걸까요??




by 마농 [2013.02.22 17:22:54]

제가 말한 부분은 정렬에서의 템프공간 사용량 차이입니다.
날짜 조건으로 데이터 읽어오는 부분은 두개 다 동일합니다.
읽어온 데이터를 정렬하는 과정에서 필요한 템프공간에 차이가 나는 것을 말합니다.


by 마농 [2013.02.22 17:30:46]
 예를 들면 날짜 조건으로 읽어 온 데이터가 대용량인 경우
한 10만건 정도 된다고 가정하고
10만건을 읽어오는 과정(풀스캔이든 인덱스 스캔이든)은 동일합니다.
다음 정렬을 하게 되는데요.
한 페이지가 10건이라고 가정했을 때
10만건을 모두 정렬하는 것 또한 동일합니다.
다만 저장공간을 10개만 사용할지? 10만개를 사용할지가 달라집니다.
row_number 만 사용하면 10만개를 읽어 가면서 10개만 저장되는 것이죠.
그러나 다른 분석함수를 함께 사용하게 되면 이렇게 동작하지 않습니다.

by 엉재 [2013.02.22 17:48:42]

시간은 정확하게 잴 수는 없었지만 10046 트레이스를 떠보니

마농님이 얘기해주신대로 저장공간을 모두 인라인뷰에서 나온 건수대로 사용을 하게 됨을 확인했습니다.

쿼리를 분리할지 하나로 합칠지는 댓글처럼 더 꼼꼼하게 비교해봐야 결과가 나오겠네요.

대신 FETCH해서 변수 할당 하는 것은 자꾸만 에러가 나고 있네요.ㅜㅜ

답변 감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입