oracle count(*) 관련 도와주세요..

Question

SELECT PAGING.* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY null) AS ROW_NUM, ROWNUMSELECT.* FROM ( SELECT TA2.* FROM ( SELECT TA.*, NVL(point_1,0)&#43;NVL(point_2,0) AS point_SUM FROM ( SELECT A.MEMBERNAME ,A.MEMBERID ,A.MEMBERNUM ,A.LEVEL_SEQ ,A.STATUS , CASE WHEN #volun# &#61; 'S' THEN (SELECT DISTINCT point FROM MVIEW2 WHERE MEMBERNO &#61; A.MEMBERNUM AND SBJT &#61; 'A') ELSE (SELECT DISTINCT point FROM MVIEW2 WHERE MEMBERNO &#61; A.MEMBERNUM AND SBJT &#61; 'B') END AS point_1 , CASE WHEN #volun# &#61; 'S' THEN (SELECT DISTINCT point FROM MVIEW2 WHERE MEMBERNO &#61; A.MEMBERNUM AND SBJT &#61; 'C') ELSE (SELECT DISTINCT point FROM MVIEW2 WHERE MEMBERNO &#61; A.MEMBERNUM AND SBJT &#61; 'D') END AS point_2 FROM TBLMEMBER A WHERE 1&#61;1 AND A.LEVEL_SEQ &#61; '2' ) TA ) TA2 WHERE 1&#61;1 and point_SUM in ('1') ) ROWNUMSELECT ) PAGING WHERE ROW_NUM BETWEEN (#currentpage#-1)*#pagesize#&#43;1 AND #currentpage#*#pagesize#SELECT COUNT(*) AS TOTALCOUNT FROM ( SELECT ROW_NUMBER() OVER (ORDER BY null) AS ROW_NUM, ROWNUMSELECT.* FROM ( SELECT TA2.* FROM ( SELECT TA.*, NVL(point_1,0)&#43;NVL(point_2,0) AS point_SUM FROM ( SELECT A.MEMBERNAME ,A.MEMBERID ,A.MEMBERNUM ,A.LEVEL_SEQ ,A.STATUS , CASE WHEN #volun# &#61; 'S' THEN (SELECT DISTINCT point FROM MVIEW2 WHERE MEMBERNO &#61; A.MEMBERNUM AND SBJT &#61; 'A') ELSE (SELECT DISTINCT point FROM MVIEW2 WHERE MEMBERNO &#61; A.MEMBERNUM AND SBJT &#61; 'B') END AS point_1 , CASE WHEN #volun# &#61; 'S' THEN (SELECT DISTINCT point FROM MVIEW2 WHERE MEMBERNO &#61; A.MEMBERNUM AND SBJT &#61; 'C') ELSE (SELECT DISTINCT point FROM MVIEW2 WHERE MEMBERNO &#61; A.MEMBERNUM AND SBJT &#61; 'D') END AS point_2 FROM TBLMEMBER A WHERE 1&#61;1 AND A.LEVEL_SEQ &#61; '2' ) TA ) TA2 WHERE 1&#61;1 and point_SUM in ('1') ) ROWNUMSELECT ) PAGING 첫번째가 리스트조회시 10개씩 가져오는거두번쨰가 전체 카운팅 하는겁니다. 노란색으로 표시된 and point_SUM in ('1') 라는 조건이 없을떄는 두쿼리 전부 0.003오 이내로 동작합니다.. 근데 노란색 조건이 붙게되니까 첫번째 쿼리는 마찬가지로 빠르게 조회되는데요두번째 카운팅은 쿼리가 뻉뻉이 도네요...(계속 수행중이라고 뜨기만 하네요...)뭐가 문제일까요..ㅜㅜ

리제네아 · Answer

in은 인덱스를 타지 않기 때문에 그럴 겁니다.

nathak · Answer

우선 실행계획을 살펴봇야죠

oracle count(*) 관련 도와주세요..

2개의 답변

Q&A 목록