oracle count(*)만 안되네요.. 이거어떻게 해야하나요...

Question

SELECT PAGING.* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY null) AS ROW_NUM, ROWNUMSELECT.* FROM ( SELECT TA2.* FROM ( SELECT TA.*, NVL(ACQ_PNT_1,0)&#43;NVL(ACQ_PNT_2,0) AS ACQ_PNT_SUM FROM ( SELECT A.MEMBERNAME ,A.MEMBERID ,A.MEMBERNUM ,A.CAMPUSID ,A.CAMPUSNAME ,A.DEPARTCD ,A.DEPARTNAME ,A.COLLEGECD ,A.COLLEGENAME ,A.MAJORCODE ,A.MAJORNAME ,A.CELNO ,A.TELNO ,A.EMAIL ,A.LEVEL_SEQ ,A.STATUS , (SELECT NM FROM SYS210 WHERE LCD &#61; 'UHJ02' AND SCD &#61; A.STATUS) AS ISATTENDINGNM , (SELECT NVL(SUM(TOTALTIME),0) FROM TBLVOLUNTEERACTIVE WHERE CREATEDBY &#61; A.MEMBERID AND VOLUNTEERDIVISION &#61; 'S' AND STATE IN ('2','5','6')) AS TOTALTIME , (SELECT NVL(SUM(TOTALTIME),0) FROM TBLVOLUNTEERACTIVE WHERE CREATEDBY &#61; A.MEMBERID AND VOLUNTEERDIVISION &#61; 'S' AND STATE IN ('2','5','6') AND APPROVEYN &#61; 'Y') AS TOTALTIME_APPROVE , (SELECT NVL(SUM(TOTALTIME),0) FROM TBLVOLUNTEERACTIVE WHERE CREATEDBY &#61; A.MEMBERID AND VOLUNTEERDIVISION &#61; 'S' AND STATE IN ('2') AND APPROVEYN &#61; 'Y' AND VOLUNTEERAPPLYNO IS NULL) AS TOTALTIME_POSSIBLE , CASE WHEN 'S' &#61; 'S' THEN (SELECT DISTINCT ACQ_PNT FROM TBLSUBJECTVOLUNTEER WHERE MEMBERNO &#61; A.MEMBERNUM AND SBJT_NO &#61; '09927') ELSE (SELECT DISTINCT ACQ_PNT FROM TBLSUBJECTVOLUNTEER WHERE MEMBERNO &#61; A.MEMBERNUM AND SBJT_NO &#61; '38063') END AS ACQ_PNT_1 , CASE WHEN 'S' &#61; 'S' THEN (SELECT DISTINCT ACQ_PNT FROM TBLSUBJECTVOLUNTEER WHERE MEMBERNO &#61; A.MEMBERNUM AND SBJT_NO &#61; '09928') ELSE (SELECT DISTINCT ACQ_PNT FROM TBLSUBJECTVOLUNTEER WHERE MEMBERNO &#61; A.MEMBERNUM AND SBJT_NO &#61; '38064') END AS ACQ_PNT_2 FROM TBLMEMBER A WHERE 1&#61;1 AND A.LEVEL_SEQ &#61; '2' ) TA ) TA2 WHERE 1&#61;1 and TA2.ACQ_PNT_SUM &#61; 1 ) ROWNUMSELECT WHERE 1&#61;1 ) PAGING ; SELECT COUNT(*) AS cnt FROM ( SELECT ROW_NUMBER() OVER (ORDER BY null) AS ROW_NUM, ROWNUMSELECT.* FROM ( SELECT TA2.* FROM ( SELECT TA.*, NVL(ACQ_PNT_1,0)&#43;NVL(ACQ_PNT_2,0) AS ACQ_PNT_SUM FROM ( SELECT A.MEMBERNAME ,A.MEMBERID ,A.MEMBERNUM ,A.CAMPUSID ,A.CAMPUSNAME ,A.DEPARTCD ,A.DEPARTNAME ,A.COLLEGECD ,A.COLLEGENAME ,A.MAJORCODE ,A.MAJORNAME ,A.CELNO ,A.TELNO ,A.EMAIL ,A.LEVEL_SEQ ,A.STATUS , (SELECT NM FROM SYS210 WHERE LCD &#61; 'UHJ02' AND SCD &#61; A.STATUS) AS ISATTENDINGNM , (SELECT NVL(SUM(TOTALTIME),0) FROM TBLVOLUNTEERACTIVE WHERE CREATEDBY &#61; A.MEMBERID AND VOLUNTEERDIVISION &#61; 'S' AND STATE IN ('2','5','6')) AS TOTALTIME , (SELECT NVL(SUM(TOTALTIME),0) FROM TBLVOLUNTEERACTIVE WHERE CREATEDBY &#61; A.MEMBERID AND VOLUNTEERDIVISION &#61; 'S' AND STATE IN ('2','5','6') AND APPROVEYN &#61; 'Y') AS TOTALTIME_APPROVE , (SELECT NVL(SUM(TOTALTIME),0) FROM TBLVOLUNTEERACTIVE WHERE CREATEDBY &#61; A.MEMBERID AND VOLUNTEERDIVISION &#61; 'S' AND STATE IN ('2') AND APPROVEYN &#61; 'Y' AND VOLUNTEERAPPLYNO IS NULL) AS TOTALTIME_POSSIBLE , CASE WHEN 'S' &#61; 'S' THEN (SELECT DISTINCT ACQ_PNT FROM TBLSUBJECTVOLUNTEER WHERE MEMBERNO &#61; A.MEMBERNUM AND SBJT_NO &#61; '09927') ELSE (SELECT DISTINCT ACQ_PNT FROM TBLSUBJECTVOLUNTEER WHERE MEMBERNO &#61; A.MEMBERNUM AND SBJT_NO &#61; '38063') END AS ACQ_PNT_1 , CASE WHEN 'S' &#61; 'S' THEN (SELECT DISTINCT ACQ_PNT FROM TBLSUBJECTVOLUNTEER WHERE MEMBERNO &#61; A.MEMBERNUM AND SBJT_NO &#61; '09928') ELSE (SELECT DISTINCT ACQ_PNT FROM TBLSUBJECTVOLUNTEER WHERE MEMBERNO &#61; A.MEMBERNUM AND SBJT_NO &#61; '38064') END AS ACQ_PNT_2 FROM TBLMEMBER A WHERE 1&#61;1 AND A.LEVEL_SEQ &#61; '2' ) TA ) TA2 WHERE 1&#61;1 and TA2.ACQ_PNT_SUM &#61; 1 ) ROWNUMSELECT WHERE 1&#61;1 ) PAGING 첫번째건 조회 잘됩니다. 빠르구요..노란색 조건 제외해도 잘되요 두번째건 조회 하면 sqlgate 뺑뺑이 도네요...그런데 이상한건 노란색 조건 제외하고 조회하면 약 4만건으로 잘 조회됩니다. 속도도 0.1초 이내로요... 문제가 뭘까요 ㅜㅜ... *로 조회는 되는데 count(*)로 하면 안된다! 이런경우는 첨이네요..쿼리실행계획도 실행시 뻗어버리니 확인이 불가하구요 ㅜㅜ ) 쿼리 짜다가 두번째꺼 몇번 날리니 db서버 모니터링 담당자가 전화와서 해당 쿼리 날리지 말라고 하네요..ㅜㅜ난감해졋어요

스텁 · Accepted Answer

쿼리가 좀 난감하네요 인라인뷰 너무 많이 씀. 저거 데이터 갯수 적은 코드성인거 맞죠? 쿼리 성능개선은 실 데이터 갖고 돌려가면서 플랜 떠봐야 하는거라 이렇게만 보고 어디가 문제인지 찾기 힘들지만 일단 쿼리 한번 돌릴때마다 인라인 뷰도 다 제각각 돌거고 인라인 뷰 말고 그냥 JOIN 써도 될거 같은데요 일단 데이터 갯수 많으면 NESTED LOOP 말고 풀스캔 타고 HASH_JOIN 이런걸로 타는게 더 나을수도 있고요. 그리고 where 절 때문에 필터링 하려고 NL여러번 탄다음에 그다음에 또 풀스캔 타는거 같은데요. 돌려보면서 플랜 봐야 하는데 회사에 쿼리 잘하시는분한테 직접 요청하시는 방법밖에 없어보입니다. 쓰고보니 도움은 안되네요;;

suny4 · Answer

첫번째는 PAGING.* 로 조회 했는데 카운트는 왜 *인거죠?count(PAGING.*) 로 하셔야 하는거 아니에요?

whdckstngh · Answer

그렇게 해도 결과는 같더라구요..ㅜㅜ 난감난감 ㅜㅜ

whdckstngh · Answer

답변 감사합니다.. 데이터가 많긴 많습니다. 40만건인데 *는 되지만 count(*) 에 인덱스 안된 조건 붙으니깐 풀스캔 하면서 뻗어버리네요..회사에서 젤 잘하는 사람한테 요청했지만 결국 해결 못했네용 ㅜㅜ 담당자랑 데이터를 줄이는 조건을 줘보기로 하고있습니다..

oracle count(*)만 안되네요.. 이거어떻게 해야하나요...

4개의 답변

Q&A 목록