With문 쓰면 빠른가요?
가상테이블을 메모리에서 만들고 접근해서 빠르다는데 무슨말인지;; 조회한다음에는 삭제되고..
토드에서 실행계획도 뽑아봤는데 XML로.. 도통 뭐가 모르겠다는;; 전부 Full Scan 하고 있는건 보이는데..
Index도 걸긴걸었고.. 조금만 도와주세요.. 쿼리 한번 올려보겠습니다.
SELECT X.*
FROM (
SELECT ROWNUM AS NUM, Y.*
FROM (
SELECT
A.INQ_HIST_NO ,
B.INST_CD ,
SUBSTR(I.DESC_V, 0,1) || SUBSTR(G.DESC_V, 0,1) AS SCHL_GRADE ,
D.NAME_V AS PISU_INST_NM ,
A.RG_INST_CD ,
C.NAME_V AS RG_INST_NM ,
A.RG_ID ,
E.NAME_V AS RG_ID_NM ,
B.INQ_CD ,
F.CD_NM AS INQ_NM ,
CASE WHEN A.NAME IS NOT NULL AND A.BMD IS NOT NULL THEN H.NAME_V || ' <![CDATA[<br />]]> ' || A.NAME || ' ( ' || A.BMD || ' ) '
WHEN A.NAME IS NOT NULL AND A.BMD IS NULL THEN H.NAME_V || ' <![CDATA[<br />]]> ' || A.NAME
WHEN A.NAME IS NULL AND A.BMD IS NOT NULL THEN H.NAME_V || ' <![CDATA[<br />]]> ' || A.BMD
WHEN A.NAME IS NULL AND A.BMD IS NULL THEN H.NAME_V
END AS SRCH_CNDTN ,
CASE WHEN A.NAME IS NOT NULL AND A.BMD IS NOT NULL THEN H.NAME_V || ' , ' || A.NAME || ' ( ' || A.BMD || ' ) '
WHEN A.NAME IS NOT NULL AND A.BMD IS NULL THEN H.NAME_V || ' , ' || A.NAME
WHEN A.NAME IS NULL AND A.BMD IS NOT NULL THEN H.NAME_V || ' , ' || A.BMD
WHEN A.NAME IS NULL AND A.BMD IS NULL THEN H.NAME_V
END AS SRCH_CNDTN_XLS ,
SUBSTR(A.RG_DTM, 0, 8) AS RG_DTM
FROM TBL_INQ_HIST A
JOIN TBL_ORGANIZATION C ON A.RG_INST_CD = C.ORG_CD_V
JOIN TBL_ORGANIZATION D ON C.TORG_CD_V = D.ORG_CD_V
JOIN TBL_MEMBER E ON A.RG_ID = E.MEMBER_ID_V AND A.RG_INST_CD = E.MEMBER_CD_V
JOIN TBL_CODE G ON C.GRADE_N = G.CODE_N
JOIN TBL_ORGANIZATION H ON A.SCHL = H.ORG_CD_V
LEFT OUTER JOIN (SELECT INQ_HIST_NO ,
MAX(INST_CD) AS INST_CD ,
MAX(INQ_CD) AS INQ_CD ,
MAX(GRDTN_DT) AS GRDTN_DT
FROM TBL_INQ_HIST_DTS
GROUP BY INQ_HIST_NO )B
ON A.INQ_HIST_NO = B.INQ_HIST_NO
LEFT OUTER JOIN TBL_COM_CD F ON B.INQ_CD = F.CD AND F.UPR_CD = 'HST004'
LEFT OUTER JOIN TBL_CODE I ON C.EST_N = I.CODE_N
ORDER BY A.INQ_HIST_NO DESC ) Y
WHERE ROWNUM <=10 ) X
WHERE NUM >=1