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