order by 속도 문제.. 도와주세요. ㅠㅠ 0 15 281

by 해피민트 [Oracle Tuning] [2018.09.13 19:04:23]


-- order by 로 인해 쿼리속도가 현저히 느려지는데 해결방법을 찾지 못해 도움을 구합니다. ㅠㅠ
-- order by 없이 실행하면 1초 order by 넣고 실행하면 7초이상 걸립니다.
-- 제일 난감한 부분은 정렬기준인 order_dtm이 테이블 내의 고정값이 아니고 
-- 함수를 통해 생성되는 가변적인 임의의 값이라는 점입니다.ㅠㅠ
-- 어떻게 해야 속도를 줄일 수 있을까요? 

SELECT
    fn_get_order_dtm(board.bs_cd, A.bs_no, member.us_memberid,'변수값') AS order_dtm,
    board.attch_file_no AS bsfileno,
    member.attch_file_no AS memfileno,
    member.us_memberid AS usmemberid,
    prod.prod_name AS prodnm
FROM
    tb_board board
    INNER JOIN tb_member member ON board.rgter_id = member.memberid
    LEFT OUTER JOIN tb_prod prod ON board.prod_no = prod.prod_no
WHERE
    board.del_yn = 'N'
ORDER BY order_dtm DESC

 

-- fn_get_order_dtm함수 구문 요약본입니다.
-- 변수 타입에 따라 지정된 각 테이블을 검색하여 가장 최근 데이터의 등록일시를 가져옵니다.
-- 그렇기 때문에 누가 호출하느냐에 따라 결과값이 바뀌고,
-- 동일한 사람이 호출하더라도 언제 실행되느냐에 따라 결과값이 또 바뀝니다. 

create or replace FUNCTION           fn_get_order_dtm
(
   ARG_BSCD IN VARCHAR2,
   ARG_BSNO IN NUMBER,
   ARG_LOGIN_MEMBERID IN VARCHAR2,
   ARG_NEWSRANK_TYPE IN NUMBER
)
RETURN DATE IS
    RTN_DTM DATE  := '';
BEGIN
    RTN_DTM := '';
    IF ARG_NEWSRANK_TYPE = 1 THEN
       SELECT BOARDSHOWH.RGSTN_DTM
              INTO RTN_DTM
              FROM TB_BOARDSHOW_H BOARDSHOWH
              WHERE BOARDSHOWH.BS_CD = ARG_BSCD
                    AND BOARDSHOWH.BS_NO = ARG_BSNO
                    AND BOARDSHOWH.BSH_CD = '04301'
                    AND FN_GET_MEMFOLWING_S(ARG_LOGIN_MEMBERID,BOARDSHOWH.RGTER_ID) = 1
                    AND FN_GET_MEMCUT_S(ARG_LOGIN_MEMBERID,BOARDSHOWH.RGTER_ID) = 0
                    AND FN_GET_MEMDEL_S(BOARDSHOWH.RGTER_ID) = 0
                    AND ROWNUM = 1
                    ORDER BY BOARDSHOWH.RGSTN_DTM DESC;
    ELSIF ARG_NEWSRANK_TYPE = 2 THEN             
          SELECT MEMPOINT.RGSTN_DTM
                 INTO RTN_DTM
                 FROM TB_MEMPOINT_H MEMPOINT
                 WHERE MEMPOINT.BS_CD = ARG_BSCD
                       AND MEMPOINT.BS_NO = ARG_BSNO
                       AND MEMPOINT.HIS_TYPE = '04201'
                       AND MEMPOINT.HIS_SANC = '02904'
                       AND FN_GET_MEMFOLWING_S(ARG_LOGIN_MEMBERID,MEMPOINT.HIS_MEMBERID) = 1
                       AND FN_GET_MEMCUT_S(ARG_LOGIN_MEMBERID,MEMPOINT.US_MEMBERID) = 0
                       AND FN_GET_MEMDEL_S(MEMPOINT.US_MEMBERID) = 0
                       AND ROWNUM = 1
                       ORDER BY MEMPOINT.RGSTN_DTM DESC;
    ELSIF ARG_NEWSRANK_TYPE = 3 THEN             
          SELECT BSFRI.RGSTN_DTM
                 INTO RTN_DTM
                 FROM TB_BSFRI_M BSFRI
                 WHERE BSFRI.BS_CD = ARG_BSCD
                       AND BSFRI.BS_NO = ARG_BSNO
                       AND FN_GET_MEMFOLWING_S(ARG_LOGIN_MEMBERID,BSFRI.RGTER_ID) = 1
                       AND FN_GET_MEMCUT_S(ARG_LOGIN_MEMBERID,BSFRI.RGTER_ID) = 0
                       AND FN_GET_MEMDEL_S(BSFRI.RGTER_ID) = 0
                       AND ROWNUM = 1
                       ORDER BY BSFRI.RGSTN_DTM DESC;
    ELSE
          RTN_DTM := '';
    END IF;
    RETURN RTN_DTM;
END fn_get_order_dtm;

 

by 아발란체 [2018.09.13 19:25:21]

정렬 항목도 인덱스를 태워야 할 것 같습니다.

함수도 인덱스를 태울 수 있습니다.

FBI(Function Based Index)라고 엑시엄에서 올린 강좌가 있네용.

http://www.gurubee.net/lecture/2639


by 해피민트 [2018.09.13 19:46:22]

답변 감사합니다!

그런데 또 궁금한 게 있습니다 ㅠ.ㅠ

함수에 입력해야하는 '변수값' 이 board 테이블의 데이터가 아니고  member 테이블의 회원아이디 입니다.

그리고 함수의 결과값은 누가, 언제 호출하냐에 따라 값이 계속 바뀝니다.

결국 동일한 레코드라도 호출될때마다 정렬순서가 바뀌게 되는 것인데요..ㅠㅠ

함수를 통해 뽑아내는 정렬항목이 고정값이 아니고 가변값이 되는 건데..

이런 상황에서도 함수기반 인덱스를 사용할 수 가 있을까요?

 


by 우리집아찌 [2018.09.14 09:11:19]

해피민트 //

동일한 변수를 넣어도 결과값이 달라질수있나요?


by 해피민트 [2018.09.14 10:06:04]

넵 맞습니다~ 동일한 변수를 넣어도 결과값이 달라집니다. ㅠㅠ


by 야신 [2018.09.14 07:37:20]

fn_get_order_dtm 함수내용을 sql로 풀어야 할것 같네요


by 해피민트 [2018.09.14 10:21:40]

넵 본문내용에 추가로 넣어놓았습니다.ㅠ0ㅠ


by 우리집아찌 [2018.09.14 10:45:50]

와우~ 펑션에서 또 평션을 호출하네요.

쉽지 않겠는데요..


by 해피민트 [2018.09.14 11:53:44]

네.. 환장할 노릇입니다 ㅠㅠ


by 소주쵝오 [2018.09.14 11:27:01]
함수내의 조건절중 rownum = 1 과 order by를 같이 사용하셨네요~
운이 좋아 order by 의 컬럼으로 인덱스가 풀렸다면 원하는 결과를 얻겠지만
그게 아니라면 의도한 결과값이 안나올수도 있겠는데요?

 


by 해피민트 [2018.09.14 11:54:21]

결과 자체는 원하는대로 나오긴 하더군요 ㅠㅠ


by 야신 [2018.09.14 16:51:16]

Where 절 함수라니...느릴만 하네요.

쉽지 않겠네요


by 야신 [2018.09.15 18:26:38]
SELECT
    (  SELECT BOARDSHOWH.RGSTN_DTM
              FROM TB_BOARDSHOW_H BOARDSHOWH
              WHERE BOARDSHOWH.BS_CD = board.bs_cd
              AND BOARDSHOWH.BS_NO = A.bs_no
              AND BOARDSHOWH.BSH_CD = '04301'
              AND FN_GET_MEMFOLWING_S(member.us_memberid,BOARDSHOWH.RGTER_ID) = 1
              AND FN_GET_MEMCUT_S(member.us_memberid,BOARDSHOWH.RGTER_ID) = 0
              AND FN_GET_MEMDEL_S(BOARDSHOWH.RGTER_ID) = 0
              AND ROWNUM = 1
              AND 변수값 = 1
          UNION ALL 
          SELECT MEMPOINT.RGSTN_DTM
                 INTO RTN_DTM
                 FROM TB_MEMPOINT_H MEMPOINT
                 WHERE MEMPOINT.BS_CD = board.bs_cd
                 AND MEMPOINT.BS_NO = A.bs_no
                 AND MEMPOINT.HIS_TYPE = '04201'
                 AND MEMPOINT.HIS_SANC = '02904'
                 AND FN_GET_MEMFOLWING_S(member.us_memberid,MEMPOINT.HIS_MEMBERID) = 1
                 AND FN_GET_MEMCUT_S(member.us_memberid,MEMPOINT.US_MEMBERID) = 0
                 AND FN_GET_MEMDEL_S(MEMPOINT.US_MEMBERID) = 0
                 AND ROWNUM = 1
                 AND 변수값 = 2
          UNION ALL
          SELECT BSFRI.RGSTN_DTM
                 INTO RTN_DTM
                 FROM TB_BSFRI_M BSFRI
                 WHERE BSFRI.BS_CD = board.bs_cd
                 AND BSFRI.BS_NO = A.bs_no
                 AND FN_GET_MEMFOLWING_S(member.us_memberid,BSFRI.RGTER_ID) = 1
                 AND FN_GET_MEMCUT_S(member.us_memberid,BSFRI.RGTER_ID) = 0
                 AND FN_GET_MEMDEL_S(BSFRI.RGTER_ID) = 0
                 AND ROWNUM = 1
                 AND 변수값 = 3
    ) AS order_dtm
--    fn_get_order_dtm(board.bs_cd, A.bs_no, member.us_memberid,'변수값') AS order_dtm,
    board.attch_file_no AS bsfileno,
    member.attch_file_no AS memfileno,
    member.us_memberid AS usmemberid,
    prod.prod_name AS prodnm
FROM
    tb_board board
    INNER JOIN tb_member member ON board.rgter_id = member.memberid
    LEFT OUTER JOIN tb_prod prod ON board.prod_no = prod.prod_no
WHERE
    board.del_yn = 'N'
ORDER BY order_dtm DESC

일단 이렇게라도 해서 함 테스트 해보시지요.


by 해피민트 [2018.09.17 16:06:26]

우선 같이 고민해주시고 답변해주셔서 정말 감사합니다.

그런데 위와 같이 할 경우 order_dtm에 2개이상의 행이 리턴되어 에러가 발생합니다. ㅠ.ㅠ

 


by 마농 [2018.09.17 16:24:58]

변수값에 따라 union all 3개 쿼리중 1개만 실행될텐데요? 2건이 나오는게 이상하네요?
에러와는 별개로 위 쿼리는 Descendin 정렬이 안되어 원하는 결과(최신날짜) 가 안나올 듯합니다.
각각에 INDEX_DESC 힌트를 줘야 할 듯


by 마농 [2018.09.17 16:25:15]
함수사용 부분을 dual 을 이용한 스칼라 서브쿼리로 바꿔 보세요.
스칼라 서브쿼리 결과 캐싱 효과를 기대해 보세요.
중복값이 많다면? 효과가 있겠지만. 중복값이 거의 없다면? 효과가 없습니다.
 - 변경전 : SELECT fn_get_order_dtm(board.bs_cd, A.bs_no, member.us_memberid, '변수값') AS order_dtm
 - 변경후 : SELECT (SELECT fn_get_order_dtm(board.bs_cd, A.bs_no, member.us_memberid, '변수값') FROM dual) AS order_dtm

 

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