whdckstngh
1k
2018-03-13 18:25:03
2
191

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



SELECT PAGING.*
  FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY null) AS ROW_NUM, ROWNUMSELECT.*
     FROM
      (
        SELECT TA2.* 
        FROM 
        (
          SELECT TA.*, NVL(point_1,0)+NVL(point_2,0) AS point_SUM
           FROM
           (
            SELECT A.MEMBERNAME
              ,A.MEMBERID
              ,A.MEMBERNUM
              ,A.LEVEL_SEQ
              ,A.STATUS
              , CASE WHEN #volun# = 'S' THEN (SELECT DISTINCT point FROM MVIEW2 WHERE MEMBERNO = A.MEMBERNUM AND SBJT = 'A') 
                 ELSE (SELECT DISTINCT point FROM MVIEW2 WHERE MEMBERNO = A.MEMBERNUM AND SBJT = 'B') END AS point_1
              , CASE WHEN #volun# = 'S' THEN (SELECT DISTINCT point FROM MVIEW2 WHERE MEMBERNO = A.MEMBERNUM AND SBJT = 'C')
                 ELSE (SELECT DISTINCT point FROM MVIEW2 WHERE MEMBERNO = A.MEMBERNUM AND SBJT = 'D') END AS point_2
         FROM TBLMEMBER A
        WHERE 1=1
          AND A.LEVEL_SEQ = '2'
          
          ) TA
        ) TA2
        WHERE 1=1
        and point_SUM in ('1')
     ) ROWNUMSELECT
  ) PAGING
  WHERE ROW_NUM BETWEEN (#currentpage#-1)*#pagesize#+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)+NVL(point_2,0) AS point_SUM
           FROM
           (
            SELECT A.MEMBERNAME
              ,A.MEMBERID
              ,A.MEMBERNUM
              ,A.LEVEL_SEQ
              ,A.STATUS
              , CASE WHEN #volun# = 'S' THEN (SELECT DISTINCT point FROM MVIEW2 WHERE MEMBERNO = A.MEMBERNUM AND SBJT = 'A') 
                 ELSE (SELECT DISTINCT point FROM MVIEW2 WHERE MEMBERNO = A.MEMBERNUM AND SBJT = 'B') END AS point_1
              , CASE WHEN #volun# = 'S' THEN (SELECT DISTINCT point FROM MVIEW2 WHERE MEMBERNO = A.MEMBERNUM AND SBJT = 'C')
                 ELSE (SELECT DISTINCT point FROM MVIEW2 WHERE MEMBERNO = A.MEMBERNUM AND SBJT = 'D') END AS point_2
         FROM TBLMEMBER A
        WHERE 1=1
          AND A.LEVEL_SEQ = '2'
          
          ) TA
        ) TA2
        WHERE 1=1
        and point_SUM in ('1')
     ) ROWNUMSELECT
  ) PAGING


첫번째가 리스트조회시 10개씩 가져오는거

두번쨰가 전체 카운팅 하는겁니다.


노란색으로 표시된 and point_SUM in ('1') 라는 조건이 없을떄는 두쿼리 전부 0.003오 이내로 동작합니다..


근데 노란색 조건이 붙게되니까 첫번째 쿼리는 마찬가지로 빠르게 조회되는데요

두번째 카운팅은 쿼리가 뻉뻉이 도네요...(계속 수행중이라고 뜨기만 하네요...)

뭐가 문제일까요..ㅜㅜ

0
0
  • 답변 2

  • 칸쵸
    3k
    2018-03-13 19:15:20

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

    0
  • nathak
    400
    2018-03-13 19:19:30

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

    0
  • 로그인을 하시면 답변을 등록할 수 있습니다.