이전글, 다음글 기능(LAG, LEAD) 과 인덱스 가능 여부나 SQL 개선 질문 드립니다.

이전글, 다음글에 대한 INDEX 가능 여부나 SQL 개선 방법이 있는지 부탁 드립니다.

 - 게시글 목록 SQL 이 상세보기 SQL 보다 더 빠른 결과가 나옵니다.

<질문 SQL : 게시글 상세 보기에서....>

SELECT * FROM (
   SELECT
    LAG(MSG_SEQ,1) OVER(ORDER BY NOTICE_YN DESC, REG_YMD DESC, MSG_GRP_NO DESC, MSG_ORD) AS PREV_SEQ,  
    LEAD(MSG_SEQ,1) OVER(ORDER BY NOTICE_YN DESC, REG_YMD DESC, MSG_GRP_NO DESC, MSG_ORD) AS NEXT_SEQ,  
    MSG_SEQ,
    BBS_TITLE,
    BBS_CONTENT,
    REG_ID,
    REG_DT
   FROM BBS_NOTICE_MSG_TB M
   WHERE BBS_CD='notice'
    AND DEL_YN='n'
    AND BBS_TITLE LIKE '%'||'시설'||'%'
)
WHERE MSG_SEQ=20842   

1번째 질의[ 0.17 초 , 검색 건수 : 1 ]

--------------------------------------------------
SHOW TRACE;
--------------------------------------------------

Query Plan:
  TABLE SCAN (M)

  rewritten query: (select lag(M.MSG_SEQ, 1, null) over (order by 10 desc , 11 desc , 12 desc , 13), lead(M.MSG_SEQ, 1, null) over (order by 10 desc , 11 desc , 12 desc , 13), M.MSG_SEQ, M.BBS_TITLE, M.REG_ID, M.REG_DT from BBS_NOTICE_MSG_TB M where M.BBS_TITLE like  ?:1  and M.DEL_YN= ?:2  and M.BBS_CD= ?:3 )

  TABLE SCAN (__t1)

  rewritten query: select [__t1].PREV_SEQ, [__t1].NEXT_SEQ, [__t1].MSG_SEQ, [__t1].BBS_TITLE, [__t1].REG_ID, [__t1].REG_DT from (select lag(M.MSG_SEQ, 1, null) over (order by 10 desc , 11 desc , 12 desc , 13) as [PREV_SEQ], lead(M.MSG_SEQ, 1, null) over (order by 10 desc , 11 desc , 12 desc , 13) as [NEXT_SEQ], M.MSG_SEQ, M.BBS_TITLE, M.REG_ID, M.REG_DT from BBS_NOTICE_MSG_TB M where M.BBS_TITLE like  ?:1  and M.DEL_YN= ?:2  and M.BBS_CD= ?:3 ) [__t1] (PREV_SEQ, NEXT_SEQ, MSG_SEQ, BBS_TITLE, REG_ID, REG_DT) where [__t1].MSG_SEQ= ?:0

Trace Statistics:
  SELECT (time: 155, fetch: 12900, ioread: 0)
    SCAN (temp time: 0, fetch: 131, ioread: 0, readrows: 354, rows: 1)
    SUBQUERY (uncorrelated)
      SELECT (time: 155, fetch: 12769, ioread: 0)
        SCAN (table: bbs_notice_msg_tb), (heap time: 83, fetch: 11821, ioread: 0, readrows: 19651, rows: 354)

============================================================================

<비교 SQL 게시글 목록>

SELECT ORDERBY_NUM() ROWNO, M.*
FROM BBS_notice_MSG_TB M
WHERE BBS_CD='notice'
 AND DEL_YN='n'
 AND BBS_TITLE LIKE '%'||'시설'||'%'
ORDER BY NOTICE_YN DESC, REG_YMD DESC, MSG_GRP_NO DESC, MSG_ORD
FOR ORDERBY_NUM() BETWEEN 1 AND 10

1번째 질의[ 0.034 초 , 검색 건수 : 10 ]

--------------------------------------------------
SHOW TRACE;
--------------------------------------------------

Query Plan:
  INDEX SCAN (M.idx_bbs_notice_msg_tb_orderby_regymd) ()
  skip order by: true

  rewritten query: select orderby_num(), M.msg_seq, M.bbs_cd, M.bbs_gcd, M.cate_cd1, M.cate_cd2, M.cate_cd3, M.msg_depth, M.msg_grp_no, M.msg_ord, M.bbs_title, M.msg_pw, M.tel_no, M.cell_no, M.email, M.addr1, M.addr2, M.zip_cd, M.spare1, M.spare2, M.spare3, M.spare4, M.spare5, M.spare6, M.spare7, M.spare8, M.spare9, M.spare10, M.view_cnt, M.notice_yn, M.notice_end_dt, M.public_yn, M.display_yn, M.display_start_dt, M.display_end_dt, M.reg_id, M.reg_dept_nm, M.reg_nm, M.reg_div, M.reg_ip, M.reg_dt, M.edit_dt, M.pin_no, M.del_yn, M.reply_mthd, M.dept_cd1, M.reply_st1, M.dept_cd2, M.reply_st2, M.dept_cd3, M.reply_st3, M.bbs_content, M.bbs_content_notag, M.bbs_wizwig_yn, M.msg_history, M.copyright_div, M.reg_ymd from BBS_notice_MSG_TB M where M.BBS_TITLE like  ?:0  and M.DEL_YN= ?:1  and M.BBS_CD= ?:2  order by 30 desc , 57 desc , 9 desc , 10 for orderby_num() between  ?:3  and  ?:4

Trace Statistics:
  SELECT (time: 14, fetch: 1723, ioread: 0)
    SCAN (index: bbs_notice_msg_tb.idx_bbs_notice_msg_tb_orderby_regymd), (btree time: 13, fetch: 1713, ioread: 0, readkeys: 8192, filteredkeys: 8192, rows: 1294) (lookup time: 8, rows: 11)