|
1. 인덱스 컬럼이 조건절에 없을때[DB 튜닝]
SELECT COUNT(Z.BBS_CD) AS count FROM
(SELECT X.BBS_CD, Y.DOMAIN_CD, X.SEQ, X.TITLE,
(SELECT CONTENTS
FROM OP B
WHERE B.SEQ = X.SEQ
) AS CONTENTS, X.SUMMARY, X.REG_DT
FROM
(SELECT BBS_CD, SEQ, TITLE, CONTENTS, SUMMARY,
REG_DT
FROM OP
WHERE 1=1
AND TITLE LIKE '%' || '장애인' || '%'
) X, OP_M Y
WHERE Y '%' ||X.BBS_CD|| '%'
GROUP BY X.BBS_CD, Y.DOMAIN_CD, X.SEQ, X.TITLE,
X.SUMMARY, X.REG_DT, Y.USER_MENU_URL
) Z, OP_D W
WHERE Z.DOMAIN_CD = W.DOMAIN_CD;
2. 조건절에 인덱스 컬럼을 만들어서 인덱스 Range Scan 을 통한 검색 속도 개선
SELECT COUNT(Z.BBS_CD) AS count FROM
(SELECT
X.BBS_CD, X.DOMAIN_CD, X.SEQ, X.TITLE,
(SELECT CONTENTS
FROM B
WHERE B.SEQ = X.SEQ
AND B.BBS_CD = X.BBS_CD
) AS CONTENTS, X.SUMMARY, X.REG_DT
FROM
(SELECT /*+ ORDERED INDEX(X OP_B_PK) */
X.BBS_CD, Y.DOMAIN_CD, X.SEQ, X.TITLE,
X.CONTENTS, X.SUMMARY, X.REG_DT,
Y.USER_MENU_URL
FROM (
(SELECT
case WHEN substr(Y.USER_MENU_URL,instr(Y.USER_MENU_URL,'bbsCd='),6) = 'bbsCd=' THEN substr(Y.USER_MENU_URL,instr(Y.USER_MENU_URL,'bbsCd=')+6,4)
WHEN
substr(Y.USER_MENU_URL,instr(Y.USER_MENU_URL,'contentUid='),11) = 'contentUid=' THEN substr(Y.USER_MENU_URL,instr(Y.USER_MENU_URL,'contentUid=')+23,4)
WHEN substr(Y.USER_MENU_URL,instr(Y.USER_MENU_URL,'ebookfile/'),10) = 'ebookfile/' THEN substr(Y.USER_MENU_URL,instr(Y.USER_MENU_URL,'ebookfile/')+25,4)
ELSE NULL
end as BBS_CD, DOMAIN_CD,
USER_MENU_URL
FROM OP_M Y
WHERE Y.USER_MENU_URL LIKE
'%' ||'bbsCd='|| '%'
OR Y.USER_MENU_URL LIKE
'%' ||'contentUid='|| '%'
OR Y.USER_MENU_URL LIKE
'%' ||'ebookfile/'|| '%'
)
UNION
(SELECT
case WHEN
substr(Y.USER_MENU_URL,instr(Y.USER_MENU_URL,'q_ctgCd='),8) = 'q_ctgCd=' THEN substr(Y.USER_MENU_URL,instr(Y.USER_MENU_URL,'q_ctgCd=')+8,4)
ELSE NULL
end BBS_CD, DOMAIN_CD,
USER_MENU_URL
FROM OP_M Y
WHERE Y.USER_MENU_URL LIKE
'%' ||'q_ctgCd='|| '%'
)
) Y, OP_B X
WHERE 1=1
AND X.TITLE LIKE '%' || '장애인' || '%'
AND X.BBS_CD = Y.BBS_CD
) X
GROUP BY X.BBS_CD, X.DOMAIN_CD, X.SEQ, X.TITLE,
X.SUMMARY, X.REG_DT, X.USER_MENU_URL
) Z, OP_D W
WHERE Z.DOMAIN_CD = W.DOMAIN_CD;
|
