오라클 쿼리 분석 및 소팅 정렬 질문 0 6 2,167

by 스마트맨 [SQL Query] 소팅 ROWNUM ORDER BY WRITE_DT DESC [2014.12.12 15:58:36]


오라클  쿼리 분석 및 소팅 정렬 질문을 드립니다.

(pageSize 수 만큼 리스트를 가져오는 과정에서 날짜별(WRITE_DT) 소팅이 제대로 되지 않는 현상인데

쿼리 수정을 어떻게 해야 날짜별 소팅이 잘될까요? )

 

SQL = SELECT NO, TYPE, TITLE, FILE1, VOD_FILE, ID, NAME, SUBSTR(WRITE_DT, 0, 4) || '.' || SUBSTR(WRITE_DT, 5, 2) || '.' || SUBSTR(WRITE_DT, 7, 2) WRITE_DT, HIT, GRADE, PUBLIC_YN, SPEC_VOD, DOWNLOAD_FLAG, DEL_RSN_CD, MIN_NO, MAX_NO
      FROM (
             SELECT /*+ INDEX_DESC(VOD_TB_PK) */ A.NO, B.CODENAME TYPE, A.TITLE TITLE, A.FILE1 FILE1, A.VOD_FILE VOD_FILE, A.ID ID, A.NAME NAME, A.WRITE_DT WRITE_DT, A.HIT HIT, A.GRADE, A.PUBLIC_YN, A.SPEC_VOD, A.DOWNLOAD_FLAG, C.CODENAME DEL_RSN_CD, MIN(A.NO) OVER () AS MIN_NO, MAX(A.NO) OVER () AS MAX_NO, ROWNUM AS RNUM
             FROM VOD_TB A, CODETABLE_TB B, CODETABLE_TB C
             WHERE 1 = 1 AND 'SMT001' = B.CATEGORY AND A.TYPE = B.CODEVALUE AND 'SMT002' = C.CATEGORY(+) AND A.DEL_RSN_CD = C.CODEVALUE(+) AND A.TYPE LIKE '%' AND A.NO <= DECODE(TRUNC(1 / 11), 0, 999999999, 1) AND A.STAT = '2' AND A.GRADE = '0' AND A.SCHOOL_CD = 'G100300007' AND A.BOARD_ID = '0' AND ROWNUM <= 10 * 10 + 1
                   ORDER BY WRITE_DT DESC
           )
      WHERE RNUM BETWEEN 10 * (DECODE(MOD(1, 10), 0, 9, MOD(1, 10) - 1)) + 1 AND 10 * DECODE(MOD(1, 10), 0, 10, MOD(1, 10))

UNION ALL

      SELECT NO, TYPE, TITLE, FILE1, VOD_FILE, ID, NAME, SUBSTR(WRITE_DT, 0, 4) || '.' || SUBSTR(WRITE_DT, 5, 2) || '.' || SUBSTR(WRITE_DT, 7, 2) WRITE_DT, HIT, GRADE, PUBLIC_YN, SPEC_VOD, DOWNLOAD_FLAG, DEL_RSN_CD, MIN_NO, MAX_NO
      FROM (
             SELECT /*+ INDEX_DESC(VOD_TB_PK) */ A.NO, B.CODENAME TYPE, A.TITLE TITLE, A.FILE1 FILE1, A.VOD_FILE VOD_FILE, A.ID ID, A.NAME NAME, A.WRITE_DT WRITE_DT, A.HIT HIT, A.GRADE GRADE, A.PUBLIC_YN PUBLIC_YN, A.SPEC_VOD SPEC_VOD, A.DOWNLOAD_FLAG DOWNLOAD_FLAG, C.CODENAME DEL_RSN_CD, MIN(A.NO) OVER () AS MIN_NO, MAX(A.NO) OVER () AS MAX_NO, ROWNUM AS RNUM
             FROM VOD_TB A, CODETABLE_TB B, CODETABLE_TB C
             WHERE 1 = 0 AND 'SMT001' = B.CATEGORY AND A.TYPE = B.CODEVALUE AND 'SMT002' = C.CATEGORY(+) AND A.DEL_RSN_CD = C.CODEVALUE(+) AND A.TYPE LIKE '%' AND A.NO <= DECODE(TRUNC(1 / 11), 0, 999999999, 1) AND A.STAT = '2' AND A.GRADE = '0' AND A.SCHOOL_CD = 'G100300007' AND A.BOARD_ID = '0' AND ROWNUM <= 10 * 10 + 1
                   ORDER BY WRITE_DT DESC
            )
      WHERE RNUM BETWEEN 10 * (DECODE(MOD(1, 10), 0, 9, MOD(1, 10) - 1)) + 1 AND 10 * DECODE(MOD(1, 10), 0, 10, MOD(1, 10))

UNION ALL

      SELECT NO, TYPE, TITLE, FILE1, VOD_FILE, ID, NAME, SUBSTR(WRITE_DT, 0, 4) || '.' || SUBSTR(WRITE_DT, 5, 2) || '.' || SUBSTR(WRITE_DT, 7, 2) WRITE_DT, HIT, GRADE, PUBLIC_YN, SPEC_VOD, DOWNLOAD_FLAG, DEL_RSN_CD, MIN_NO, MAX_NO
      FROM (
             SELECT NO, TYPE, TITLE, FILE1, VOD_FILE, ID, NAME, WRITE_DT, HIT, GRADE, PUBLIC_YN, SPEC_VOD, DOWNLOAD_FLAG, DEL_RSN_CD, MIN_NO, MAX_NO, ROWNUM AS RNUM
             FROM (
                   SELECT A.NO, B.CODENAME TYPE, A.TITLE TITLE, A.FILE1 FILE1, A.VOD_FILE VOD_FILE, A.ID ID, A.NAME NAME, A.WRITE_DT WRITE_DT, A.HIT HIT, A.GRADE GRADE, A.PUBLIC_YN PUBLIC_YN, A.SPEC_VOD SPEC_VOD, A.DOWNLOAD_FLAG DOWNLOAD_FLAG, C.CODENAME DEL_RSN_CD, MIN(A.NO) OVER (ORDER BY A.NO DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MIN_NO, MAX(A.NO) OVER () AS MAX_NO
                   FROM VOD_TB A, CODETABLE_TB B, CODETABLE_TB C
                   WHERE 1 = -1 AND 'SMT001' = B.CATEGORY AND A.TYPE = B.CODEVALUE AND 'SMT002' = C.CATEGORY(+) AND A.DEL_RSN_CD = C.CODEVALUE(+) AND A.TYPE LIKE '%' AND A.NO >= 1 AND A.STAT = '2' AND A.GRADE = '0' AND A.SCHOOL_CD = 'G100300007' AND A.BOARD_ID = '0' AND ROWNUM <= 10 * 10 + 1 )
                      ORDER BY WRITE_DT DESC
                  )
             WHERE RNUM BETWEEN 10 * (DECODE(MOD(1, 10), 0, 9, MOD(1, 10) - 1)) + 1 AND 10 * DECODE(MOD(1, 10), 0, 10, MOD(1, 10)
           )

//pageSize = 10
//page = 1

 

 

 

 

by 빈이 [2014.12.12 16:38:06]

WHERE 1 = -1

WHERE 1 = 0

이 조건이 들어간 SELECT의 경우 리스트가 무조건 0ROW 아닌가요?


by 빈이 [2014.12.12 16:45:05]

SELECT NO, TYPE, TITLE, FILE1, VOD_FILE, ID, NAME, SUBSTR(WRITE_DT, 0, 4) || '.' || SUBSTR(WRITE_DT, 5, 2) || '.' || SUBSTR(WRITE_DT, 7, 2) WRITE_DT, HIT, GRADE, PUBLIC_YN, SPEC_VOD, DOWNLOAD_FLAG, DEL_RSN_CD, MIN_NO, MAX_NO
FROM (
       SELECT /*+ INDEX_DESC(VOD_TB_PK) */
            A.NO, B.CODENAME TYPE
            , A.TITLE TITLE
            , A.FILE1 FILE1
            , A.VOD_FILE VOD_FILE
            , A.ID ID
            , A.NAME NAME
            , A.WRITE_DT WRITE_DT
            , A.HIT HIT
            , A.GRADE
            , A.PUBLIC_YN
            , A.SPEC_VOD
            , A.DOWNLOAD_FLAG
            , C.CODENAME DEL_RSN_CD
            , MIN(A.NO) OVER () AS MIN_NO
            , MAX(A.NO) OVER () AS MAX_NO
            , ROW_NUMBER() OVER( ORDER BY WRITE_DT DESC) AS RNUM
            --, ROWNUM AS RNUM <---------------------- 제거
       FROM VOD_TB A, CODETABLE_TB B, CODETABLE_TB C
       WHERE 1 = 1
       AND 'SMT001' = B.CATEGORY
       AND A.TYPE = B.CODEVALUE
       AND 'SMT002' = C.CATEGORY(+)
       AND A.DEL_RSN_CD = C.CODEVALUE(+)
       AND A.TYPE LIKE '%'
       AND A.NO <= DECODE(TRUNC(1 / 11), 0, 999999999, 1)
       AND A.STAT = '2' AND A.GRADE = '0'
       AND A.SCHOOL_CD = 'G100300007'
       AND A.BOARD_ID = '0'
--       ORDER BY WRITE_DT DESC 소팅 조건 주석처리
     )
WHERE RNUM <= 10 * 10 + 1
AND RNUM BETWEEN 10 * (DECODE(MOD(1, 10), 0, 9, MOD(1, 10) - 1)) + 1 AND 10 * DECODE(MOD(1, 10), 0, 10, MOD(1, 10))
/** 아래의 WHERE 1 = -1 , WHERE 1 = 0 조건으로 인해서 볼 필요없는듯;;*/
--UNION ALL
--SELECT NO, TYPE, TITLE, FILE1, VOD_FILE, ID, NAME, SUBSTR(WRITE_DT, 0, 4) || '.' || SUBSTR(WRITE_DT, 5, 2) || '.' || SUBSTR(WRITE_DT, 7, 2) WRITE_DT, HIT, GRADE, PUBLIC_YN, SPEC_VOD, DOWNLOAD_FLAG, DEL_RSN_CD, MIN_NO, MAX_NO
--FROM (
--       SELECT /*+ INDEX_DESC(VOD_TB_PK) */ A.NO, B.CODENAME TYPE, A.TITLE TITLE, A.FILE1 FILE1, A.VOD_FILE VOD_FILE, A.ID ID, A.NAME NAME, A.WRITE_DT WRITE_DT, A.HIT HIT, A.GRADE GRADE, A.PUBLIC_YN PUBLIC_YN, A.SPEC_VOD SPEC_VOD, A.DOWNLOAD_FLAG DOWNLOAD_FLAG, C.CODENAME DEL_RSN_CD, MIN(A.NO) OVER () AS MIN_NO, MAX(A.NO) OVER () AS MAX_NO, ROWNUM AS RNUM
--       FROM VOD_TB A, CODETABLE_TB B, CODETABLE_TB C
--       WHERE 1 = 0 AND 'SMT001' = B.CATEGORY AND A.TYPE = B.CODEVALUE AND 'SMT002' = C.CATEGORY(+) AND A.DEL_RSN_CD = C.CODEVALUE(+) AND A.TYPE LIKE '%' AND A.NO <= DECODE(TRUNC(1 / 11), 0, 999999999, 1) AND A.STAT = '2' AND A.GRADE = '0' AND A.SCHOOL_CD = 'G100300007' AND A.BOARD_ID = '0' AND ROWNUM <= 10 * 10 + 1
--             ORDER BY WRITE_DT DESC
--      )
--WHERE RNUM BETWEEN 10 * (DECODE(MOD(1, 10), 0, 9, MOD(1, 10) - 1)) + 1 AND 10 * DECODE(MOD(1, 10), 0, 10, MOD(1, 10))
--UNION ALL
--SELECT NO, TYPE, TITLE, FILE1, VOD_FILE, ID, NAME, SUBSTR(WRITE_DT, 0, 4) || '.' || SUBSTR(WRITE_DT, 5, 2) || '.' || SUBSTR(WRITE_DT, 7, 2) WRITE_DT, HIT, GRADE, PUBLIC_YN, SPEC_VOD, DOWNLOAD_FLAG, DEL_RSN_CD, MIN_NO, MAX_NO
--FROM (
--       SELECT NO, TYPE, TITLE, FILE1, VOD_FILE, ID, NAME, WRITE_DT, HIT, GRADE, PUBLIC_YN, SPEC_VOD, DOWNLOAD_FLAG, DEL_RSN_CD, MIN_NO, MAX_NO, ROWNUM AS RNUM
--       FROM (
--             SELECT A.NO, B.CODENAME TYPE, A.TITLE TITLE, A.FILE1 FILE1, A.VOD_FILE VOD_FILE, A.ID ID, A.NAME NAME, A.WRITE_DT WRITE_DT, A.HIT HIT, A.GRADE GRADE, A.PUBLIC_YN PUBLIC_YN, A.SPEC_VOD SPEC_VOD, A.DOWNLOAD_FLAG DOWNLOAD_FLAG, C.CODENAME DEL_RSN_CD, MIN(A.NO) OVER (ORDER BY A.NO DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MIN_NO, MAX(A.NO) OVER () AS MAX_NO
--             FROM VOD_TB A, CODETABLE_TB B, CODETABLE_TB C
--             WHERE 1 = -1 AND 'SMT001' = B.CATEGORY AND A.TYPE = B.CODEVALUE AND 'SMT002' = C.CATEGORY(+) AND A.DEL_RSN_CD = C.CODEVALUE(+) AND A.TYPE LIKE '%' AND A.NO >= 1 AND A.STAT = '2' AND A.GRADE = '0' AND A.SCHOOL_CD = 'G100300007' AND A.BOARD_ID = '0' AND ROWNUM <= 10 * 10 + 1 )
--                ORDER BY WRITE_DT DESC
--            )
--       WHERE RNUM BETWEEN 10 * (DECODE(MOD(1, 10), 0, 9, MOD(1, 10) - 1)) + 1 AND 10 * DECODE(MOD(1, 10), 0, 10, MOD(1, 10)
--     )
;


by 마농 [2014.12.12 16:55:39]

테이블이 하나가 아니라 여러개네요.
인덱스 힌트가 제대로 안먹을 수도 있는데
힌트가 정상동작했다고 하더라도
조인 과정에서 다시 또 정렬 순서가 틀어질 가능성이 높구요.
따라서 인덱스만 탄다고 해서 정렬결과를 얻을 수는 없습니다.
정렬 구문이 있긴 하지만
이는 ROWNUM rnum 을 구한 이후에 동작합니다.
RNUM 순서와 정렬순서가 일치하지 않는다는 의미입니다.
모든 테이블을 다 조인한 후에 페이징 처리하지 마세요.
A 만을 인덱스 태워 페이지 처리하시고
페이지 처리의 결과집합을 이용해 나머지 B, C 와 조인하세요.
인덱스 힌트도 틀렸네요
/*+ INDEX_DESC(VOD_TB_PK) */ ===> /*+ INDEX_DESC(A VOD_TB_PK) */


by 마농 [2014.12.12 17:31:25]

정렬은 날짜를 이용하는데? 인덱스는 PK 를 이용하네요?
날짜 정렬 할거면 날짜 인덱스를 이용해야 할 듯 하구요.
min_no, max_no 는 이전 이후 no 인가요?
이전 이후 버튼용이고 이걸 이전 이후 쿼리에서 이용한다면?
그렇다면 더더욱 날짜가 아닌 no 로 정렬해야 올바르겠네요.


by 스마트맨 [2014.12.13 13:34:55]


쿼리를 수정했는데 아래와 같은 결과가 나옵니다.

pagesize를 크게주어 1page에 전체결과를 출력하면 날짜별 소팅이 제대로 되는데
pagesize를 작게주어 쿼리를 하면 page별 소팅만되어 아래와 같은 현상이 나오는데
어떻게 쿼리를 수정하면 page별 날짜소팅이 제대로 될까요?

 

pagesize=10일경우

1page 출력결과
=====================
2014.03.24
2013.07.19
2013.05.31
2013.05.21
2013.02.13
2012.12.17
2012.12.17
2012.07.25
2012.06.07
2012.03.26
=====================

2page출력결과
=====================
2014.06.09
2014.04.23
2014.04.23
2014.04.23
2014.03.24
2014.02.13
2013.02.12
2012.10.15


pagesize=20일 경우

1page 출력결과
======================
2014.06.09
2014.04.23
2014.04.23
2014.04.23
2014.03.24
2014.03.24
2014.02.13
2013.07.19
2013.05.31
2013.05.21
2013.02.13
2013.02.12
2012.12.17
2012.12.17
2012.10.15
2012.07.25
2012.06.07
2012.03.26


SELECT * FROM
(
     SELECT NO, TYPE, TITLE, FILE1, VOD_FILE, ID, NAME, SUBSTR(WRITE_DT, 0, 4) || '.' || SUBSTR(WRITE_DT, 5, 2) || '.' || SUBSTR(WRITE_DT, 7, 2) WRITE_DT, HIT, GRADE, PUBLIC_YN, SPEC_VOD, DOWNLOAD_FLAG, DEL_RSN_CD, MIN_NO, MAX_NO   
     FROM
          (
             SELECT /*+ INDEX_DESC(A VOD_TB_PK) */ A.NO, B.CODENAME TYPE, A.TITLE TITLE, A.FILE1 FILE1, A.VOD_FILE VOD_FILE, A.ID ID, A.NAME NAME, A.WRITE_DT WRITE_DT, A.HIT HIT, A.GRADE, A.PUBLIC_YN, A.SPEC_VOD, A.DOWNLOAD_FLAG,
                    C.CODENAME DEL_RSN_CD, MIN(A.NO) OVER () AS MIN_NO, MAX(A.NO) OVER () AS MAX_NO, ROWNUM AS RNUM
             FROM VOD_TB A, CODETABLE_TB B, CODETABLE_TB C
             WHERE 1 = 1 AND 'SMT001' = B.CATEGORY AND A.TYPE = B.CODEVALUE AND 'SMT002' = C.CATEGORY(+) AND A.DEL_RSN_CD = C.CODEVALUE(+) AND A.TYPE LIKE '%' AND A.NO <= DECODE(TRUNC(1 / 11), 0, 999999999, 1) AND A.STAT = '2'
                         AND A.GRADE = '0' AND A.SCHOOL_CD = 'G100300007' AND A.BOARD_ID = '0' AND ROWNUM <= 20 * 10 + 1
           )
     WHERE RNUM BETWEEN 20 * (DECODE(MOD(1, 10), 0, 9, MOD(1, 10) - 1)) + 1 AND 20 * DECODE(MOD(1, 10), 0, 10, MOD(1, 10))

     UNION ALL

     SELECT NO, TYPE, TITLE, FILE1, VOD_FILE, ID, NAME, SUBSTR(WRITE_DT, 0, 4) || '.' || SUBSTR(WRITE_DT, 5, 2) || '.' || SUBSTR(WRITE_DT, 7, 2) WRITE_DT, HIT, GRADE, PUBLIC_YN, SPEC_VOD, DOWNLOAD_FLAG, DEL_RSN_CD, MIN_NO, MAX_NO
     FROM
          (
             SELECT /*+ INDEX_DESC(A VOD_TB_PK) */ A.NO, B.CODENAME TYPE, A.TITLE TITLE, A.FILE1 FILE1, A.VOD_FILE VOD_FILE, A.ID ID, A.NAME NAME, A.WRITE_DT WRITE_DT, A.HIT HIT, A.GRADE GRADE, A.PUBLIC_YN PUBLIC_YN, A.SPEC_VOD
                                                   SPEC_VOD, A.DOWNLOAD_FLAG DOWNLOAD_FLAG, C.CODENAME DEL_RSN_CD, MIN(A.NO) OVER () AS MIN_NO, MAX(A.NO) OVER () AS MAX_NO, ROWNUM AS RNUM
             FROM VOD_TB A, CODETABLE_TB B, CODETABLE_TB C
             WHERE 1 = 0 AND 'SMT001' = B.CATEGORY AND A.TYPE = B.CODEVALUE AND 'SMT002' = C.CATEGORY(+) AND A.DEL_RSN_CD = C.CODEVALUE(+) AND A.TYPE LIKE '%' AND A.NO <= DECODE(TRUNC(1 / 11), 0, 999999999, 1) AND A.STAT = '2'
                         AND A.GRADE = '0' AND A.SCHOOL_CD = 'G100300007' AND A.BOARD_ID = '0'
          )
    WHERE RNUM BETWEEN 20 * (DECODE(MOD(1, 10), 0, 9, MOD(1, 10) - 1)) + 1 AND 20 * DECODE(MOD(1, 10), 0, 10, MOD(1, 10))

    UNION ALL

    SELECT NO, TYPE, TITLE, FILE1, VOD_FILE, ID, NAME, SUBSTR(WRITE_DT, 0, 4) || '.' || SUBSTR(WRITE_DT, 5, 2) || '.' || SUBSTR(WRITE_DT, 7, 2) WRITE_DT, HIT, GRADE, PUBLIC_YN, SPEC_VOD, DOWNLOAD_FLAG, DEL_RSN_CD, MIN_NO, MAX_NO
    FROM
      (
          SELECT NO, TYPE, TITLE, FILE1, VOD_FILE, ID, NAME, WRITE_DT, HIT, GRADE, PUBLIC_YN, SPEC_VOD, DOWNLOAD_FLAG, DEL_RSN_CD, MIN_NO, MAX_NO, ROWNUM AS RNUM
          FROM
              (
                 SELECT A.NO, B.CODENAME TYPE, A.TITLE TITLE, A.FILE1 FILE1, A.VOD_FILE VOD_FILE, A.ID ID, A.NAME NAME, A.WRITE_DT WRITE_DT, A.HIT HIT, A.GRADE GRADE, A.PUBLIC_YN PUBLIC_YN, A.SPEC_VOD SPEC_VOD,
                        A.DOWNLOAD_FLAG DOWNLOAD_FLAG, C.CODENAME DEL_RSN_CD, MIN(A.NO) OVER (ORDER BY A.NO DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MIN_NO, MAX(A.NO) OVER () AS MAX_NO
                 FROM VOD_TB A, CODETABLE_TB B, CODETABLE_TB C WHERE 1 = -1 AND 'SMT001' = B.CATEGORY AND A.TYPE = B.CODEVALUE AND 'SMT002' = C.CATEGORY(+) AND A.DEL_RSN_CD = C.CODEVALUE(+) AND A.TYPE LIKE '%' AND A.NO >= 1
                      AND A.STAT = '2' AND A.GRADE = '0' AND A.SCHOOL_CD = 'G100300007' AND A.BOARD_ID = '0' AND ROWNUM <= 20 * 10 + 1
              )
      )
                 WHERE RNUM BETWEEN 20 * (DECODE(MOD(1, 10), 0, 9, MOD(1, 10) - 1)) + 1 AND 20 * DECODE(MOD(1, 10), 0, 10, MOD(1, 10))
) ORDER BY WRITE_DT DESC


by 마농 [2014.12.14 12:36:49]

Rownum 과 Order by 함께 사용시

정렬된 순번이 나오는게 아니라 순번부터 부여된 뒤 정렬합니다.

Order by 를 먼저 수행하고 한번 감싸 Rownum 하세요.

또한 위에서 언급한 여러가지 문제점들을 검토하세요.


댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입